?
Solved

Need Equation/formula for Excel

Posted on 2011-05-08
4
Medium Priority
?
324 Views
Last Modified: 2012-05-11
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
Comment
Question by:DarinOBrien
4 Comments
 
LVL 8

Assisted Solution

by:Camy
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

by:
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

by:DarinOBrien
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

by:zorvek (Kevin Jones)
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question