Articles - Page 5

Formula to compute for values if cells have values less than a specified amount

Formula

=SUMIF(range, expression)

Add the values in cells if the value is greater than 3000

=SUMIF(A1:A8,"<3000")

Add the values in cells if the value is greater than 1000

=SUMIF(A1:A8,"<1000")

Add the values in cells if the value is greater than 500

=SUMIF(A1:A8,"<500")

Formula Parameters

• Range – The first part of the formula lets you specify the range to add. This is specified like this, A1:A20
• Expression – The second part of the formula lets you specify the condition. The condition is in string form. You can use multiple logical operators like <, =, <=, >=

=SUMIF(A1:A8,"<=2000")

=SUMIF(A1:A8,">=2000")

=SUMIF(A1:A8,"=2000")

Formula to compute for values if cells have values greater than a specified amount

Formula

=SUMIF(range, expression)

Add the values in cells if the value is greater than 4000

=SUMIF(A1:A8,">4000")

Add the values in cells if the value is greater than 2000

=SUMIF(A1:A8,">2000")

Add the values in cells if the value is greater than 1000

=SUMIF(A1:A8,">1000")

Formula Parameters

• Range – The first part of the formula lets you specify the range to add.
• Expression – The second part of the formula lets you specify the condition. The condition is in string form. You can use multiple logical operators.

=SUMIF(A1:A8,">=1000")

=SUMIF(A1:A8,"<=1000")

This is not a common error in when using Ruby on Rails. This is actually something to do with Postgres, more than anything else. You however don’t have to fret. This occurs when a statement failed in a transaction. The solution is to look in the failed transaction and fix it.

A quick solution here is to restart your Ruby on Rails server.

When using ActiveRecord, we may think that you are too far from the real thing. It is actually very at implementing actual SQL statements.

What do you to get the SUM of multiple columns using ActiveRecord.

SELECT SUM(qhse+qhse_manager+managing_director+other_internal) FROM "stops"

This is very easy to implement with Rails ActiveRecord

ActiveRecord

@location_ytd_qhse = Stop.sum("qhse+qhse_manager+managing_director+other_internal")

This will produce our nice SQL Statement:

SQL Statement

SELECT SUM(qhse+qhse_manager+managing_director+other_internal) FROM "stops"

If you want to try something else, you can multiply column sum values:

ActiveRecord

@location_ytd_qhse = Stop.sum("qhse*qhse_manager*managing_director*other_internal")

SQL Statement

SELECT SUM(qhse*qhse_manager*managing_director*other_internal) FROM "stops"