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

Solved

Posted on 2011-05-08

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

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

4 Comments

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

you can nest IF statements.

=IF(sum(A1:A10)<$V$1, $V$2*A10,IF(sum(A1:A10)>$V

or better

=if(sum(A1:A10)>$V$1,if($V

cheers, teylyn

I implemented your logic exactly as you required:

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

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

Title | # Comments | Views | Activity |
---|---|---|---|

How to calculate the QUARTILE ? | 5 | 25 | |

IF OR formula Excel | 8 | 24 | |

How to Autofill Across to next value | 3 | 21 | |

Help adding more columns to my pivot table | 19 | 17 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**23** Experts available now in Live!