Need Equation/formula for Excel
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!