Solved

# Need Equation/formula for Excel

Posted on 2011-05-08
Medium Priority
324 Views
I'm working in Excel and I'm a bit of a novice.  Here's what I need an equation/formula to do in one cell:

1.  If sum of A1:A10 is less than variable#1, return value = variable#2*A10.
2.  If sum of A1:A10 is greater than variable#1, return value = variable#2 - sum of A1:A9
3.  If sum of A1:A10 is greater than variable#1, AND variable#1 - sum of A1:A10  is less than 0, the return value = 0.

NOTE:  Variable #1 and Variable #2 are actually defined in the spreadsheet by the user so they can be easily identified.

I figured out how to do the first 2 requirements with the following equation but it doesn't do the third:

=IF(sum(A1:A10)<\$V\$1, \$V\$2*A10, \$V\$2*(\$V\$1-sum(A1:A9))

How do I get it to meet requirement #3?

My apologies if there's a better way to explain this but I'm new at this!

Thanks,
Darin
0
Question by:DarinOBrien

LVL 8

Assisted Solution

Camy earned 1000 total points
ID: 35717172
Use 2 IF statements.

IF(statement3, then 0, else, IF(statement1, then return value, else return statement 2 value))
0

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 35717188
hello,

you can nest IF statements.

=IF(sum(A1:A10)<\$V\$1, \$V\$2*A10,IF(sum(A1:A10)>\$V\$1,if(\$V\$1-sum(A1:A10)>0,0,\$V\$2*(\$V\$1-sum(A1:A9)))))

or better

=if(sum(A1:A10)>\$V\$1,if(\$V\$1-sum(A1:A10)>0,0,\$V\$2*(\$V\$1-sum(A1:A9))),\$V\$2*A10)

cheers, teylyn
0

Author Closing Comment

ID: 35717297
Both are excellent solutions; wish I could give both of you 500 points.  Thanks for the speedy responses!
0

LVL 81

Expert Comment

ID: 35717354
Darin,

I implemented your logic exactly as you required:

=IF(SUM(A1:A10)<Var1,Var2*A10,(Var1>=SUM(A1:A10))*(Var2-SUM(A1:A9)))

BUT there may be an error in your logic. These two conditions always result in a zero result:

2.  If sum of A1:A10 is greater than variable#1, return value = variable#2 - sum of A1:A9
3.  If sum of A1:A10 is greater than variable#1, AND variable#1 - sum of A1:A10  is less than 0, the return value = 0.

Rule (2) states that if the sum of A1:A10 is greater than Var1 then return something. But rule (3) states that if the sum of A1:A10 is greater than Var1 AND Var1 less the sum of A1:A10 is less than zero then return zero. If the sum of A1:A10 is greater than Var1 then Var1 will ALWAYS be LESS than the sum and therefore Var1 less the sum will ALWAYS be less than zero.

Also, you don't cover if the sum of A1:A10 is equal to Var1. I assumed that that is the same as A1:A10 is greater than Var1.

Kevin
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.