Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extremely complex IF statements
I have the following statements that we need to put in a spreadsheet to do
calculations. Can anyone please help? The greater of D53 and J53 is the "chargeable" weight. If the chargeable weight x C17 is less than B17, the value should be B17. Otherwise, the value should be the chargeable weight x C17. If I53 x 363 is less than D53, then D53 x D10 is the value, except when D53 x D10 is less than C10. Then the value should be C10. Otherwise, the value should be D53 x .2617. However, if I53 x 363 is greater than D53, then I53 x D10 is the value, except, when I53 x D10 is less than C10, then the value should be C10. Otherwise, the value should be I53 x D10. If I53 x 363 is less than D53, then D53 x .0688 is the value, except when D53 x .0688 is less than B24. Then the value should be B24. Otherwise, the value should be D53 x .0688. However, if I53 x 363 is greater than D53, then I53 x D24 is the value, except when I53 x D24 is less than B24. Then the value should be B24. Otherwise, the value should be I53 x D24. If the result of D53 x C25 is less than B25, the value should be B25. However, if the Value of D53 x C25 is greater than E25, the value should be E25. Otherwise, the value should be D53 x C25. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extremely complex IF statements
I think you need to re-evaluate this. For example, you state "If I53 x
363 is less than D53, then D53 x D10 is the value, ..." at the beginning of the 3rd paragraph, and then start the 4th paragraph with "If I53 x 363 is less than D53, then D53 x .0688 is the value, ..." - which is it to be? It strikes me that you can use MIN() or MAX() in certain of your comparisons, rather than use IF, such as your second paragraph can be written as: =MAX(MAX(D53,J53)*C17,B17) Hope this helps. Pete On Jan 29, 2:52 pm, Teri wrote: I have the following statements that we need to put in a spreadsheet to do calculations. Can anyone please help? The greater of D53 and J53 is the "chargeable" weight. If the chargeable weight x C17 is less than B17, the value should be B17. Otherwise, the value should be the chargeable weight x C17. If I53 x 363 is less than D53, then D53 x D10 is the value, except when D53 x D10 is less than C10. Then the value should be C10. Otherwise, the value should be D53 x .2617. However, if I53 x 363 is greater than D53, then I53 x D10 is the value, except, when I53 x D10 is less than C10, then the value should be C10. Otherwise, the value should be I53 x D10. If I53 x 363 is less than D53, then D53 x .0688 is the value, except when D53 x .0688 is less than B24. Then the value should be B24. Otherwise, the value should be D53 x .0688. However, if I53 x 363 is greater than D53, then I53 x D24 is the value, except when I53 x D24 is less than B24. Then the value should be B24. Otherwise, the value should be I53 x D24. If the result of D53 x C25 is less than B25, the value should be B25. However, if the Value of D53 x C25 is greater than E25, the value should be E25. Otherwise, the value should be D53 x C25. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extremely complex IF statements
"Teri" wrote:
... The greater of D53 and J53 is the "chargeable" weight. If the chargeable weight x C17 is less than B17, the value should be B17. Otherwise, the value should be the chargeable weight x C17. .. One way for the above portion: =IF(MAX(D53,J53)*C17<B17,B17,MAX(D53,J53)*C17) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extremely complex IF statements
Thanks! The formula for the first statement worked perfectly, but I have NO
idea how to formulate the others........ "Teri" wrote: I have the following statements that we need to put in a spreadsheet to do calculations. Can anyone please help? The greater of D53 and J53 is the "chargeable" weight. If the chargeable weight x C17 is less than B17, the value should be B17. Otherwise, the value should be the chargeable weight x C17. If I53 x 363 is less than D53, then D53 x D10 is the value, except when D53 x D10 is less than C10. Then the value should be C10. Otherwise, the value should be D53 x .2617. However, if I53 x 363 is greater than D53, then I53 x D10 is the value, except, when I53 x D10 is less than C10, then the value should be C10. Otherwise, the value should be I53 x D10. If I53 x 363 is less than D53, then D53 x .0688 is the value, except when D53 x .0688 is less than B24. Then the value should be B24. Otherwise, the value should be D53 x .0688. However, if I53 x 363 is greater than D53, then I53 x D24 is the value, except when I53 x D24 is less than B24. Then the value should be B24. Otherwise, the value should be I53 x D24. If the result of D53 x C25 is less than B25, the value should be B25. However, if the Value of D53 x C25 is greater than E25, the value should be E25. Otherwise, the value should be D53 x C25. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extremely complex IF statements
"Teri" wrote:
Thanks! The formula for the first statement worked perfectly, but I have NO idea how to formulate the others........ As Pete hinted in his response, think you've got too many conditions to be satisfied in your posts' portions 2 & 3. Review your requirements, then post afresh. Keep it to one question per post (eg: if you have 2 questions, then put in as 2 separate posts). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extremely complex IF statements
The first and last ones can be simplified to:
=MAX(MAX(D53,J53)*C17,B17) =MEDIAN(B25,D53*C25,E25) On Jan 29, 4:15 pm, Teri wrote: Thanks! The formula for the first statement worked perfectly, but I have NO idea how to formulate the others........ "Teri" wrote: I have the following statements that we need to put in a spreadsheet to do calculations. Can anyone please help? The greater of D53 and J53 is the "chargeable" weight. If the chargeable weight x C17 is less than B17, the value should be B17. Otherwise, the value should be the chargeable weight x C17. If I53 x 363 is less than D53, then D53 x D10 is the value, except when D53 x D10 is less than C10. Then the value should be C10. Otherwise, the value should be D53 x .2617. However, if I53 x 363 is greater than D53, then I53 x D10 is the value, except, when I53 x D10 is less than C10, then the value should be C10. Otherwise, the value should be I53 x D10. If I53 x 363 is less than D53, then D53 x .0688 is the value, except when D53 x .0688 is less than B24. Then the value should be B24. Otherwise, the value should be D53 x .0688. However, if I53 x 363 is greater than D53, then I53 x D24 is the value, except when I53 x D24 is less than B24. Then the value should be B24. Otherwise, the value should be I53 x D24. If the result of D53 x C25 is less than B25, the value should be B25. However, if the Value of D53 x C25 is greater than E25, the value should be E25. Otherwise, the value should be D53 x C25.- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple IF statements or a better method ? | Excel Discussion (Misc queries) | |||
Complex Vlookup and List Validation and Nested IF statements | Excel Worksheet Functions | |||
Extremely complex problem: showing a value as an 'axis' on a circle | Excel Discussion (Misc queries) | |||
Linking two IF statements together | Excel Discussion (Misc queries) | |||
complex if statements in excel | Excel Worksheet Functions |