Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated IF statement - desperately need some help
Afternoon everyone.
Have been struggling to get a formula together for the following. I am being told due to its complexity that I will probably need to break it down into 2 sections but hoping someone can help me simplify things. I need to do the following A1 = 1 (although this can be 1,2,3,4,5) (This is my variable I am struggling with) B1 = ‚¬10000 (TARGET) C1 - ‚¬6500 (ACTUAL) Then I have a few boxes showing the following <70% between 70-99% 100% A5 = 1 B5 = 10% C5 = 15% D5 = 18% A6 = 2 B6 = 12% C6 = 18% D6 = 21% A7 = 3 B7 = 14% C7 = 21% D7 = 24% A8 = 4 B8 = 16% C8 = 24% D8 = 27% A9 = 5 B9 = 18% C9 = 27% D9 = 30% So potentially there are 3 statements 1 showing under 70% 1 greater than 70% but less than 99% 1 greater than 100% =IF(SUM(C1/B1)<70%,C1*B5 =IF(AND(SUM(C1/B1)70%<90%)),C1*C5 (I know this is wrong) My problem is I now need to add the variable which is in A1 as it determines the % applicable in the boxes above. Apologies if this is confusing but hopefully I have explained it correctly Thanks in advance for any help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated IF statement - desperately need some help
How about something like this?
1 10000 6500 < 70% 70% < 99% 100% 0% 70% 10000% 1 10% 15% 18% 2 12% 18% 21% 3 14% 21% 24% 4 16% 24% 27% 5 18% 27% 30% 650 10% ~=HLOOKUP(C1/B1,B4:D9,A1+1,TRUE)*C1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated IF statement - desperately need some help
On Mar 25, 2:46*pm, Ziggy wrote:
How about something like this? 1 * * * 10000 * 6500 * * * * < 70% * * * * 70% < 99% * * * 100% * * * * 0% * * *70% * * 10000% 1 * * * 10% * * 15% * * 18% 2 * * * 12% * * 18% * * 21% 3 * * * 14% * * 21% * * 24% 4 * * * 16% * * 24% * * 27% 5 * * * 18% * * 27% * * 30% * * * * 650 * * * * * * 10% * * * * ~=HLOOKUP(C1/B1,B4:D9,A1+1,TRUE)*C1 Oops, that 10000% should now be 100%. I played with the formula and didn't change that. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated IF statement - desperately need some help
On Mar 25, 3:28*pm, Ziggy wrote:
On Mar 25, 2:46*pm, Ziggy wrote: How about something like this? 1 * * * 10000 * 6500 * * * * < 70% * * * * 70% < 99% * * * 100% * * * * 0% * * *70% * * 10000% 1 * * * 10% * * 15% * * 18% 2 * * * 12% * * 18% * * 21% 3 * * * 14% * * 21% * * 24% 4 * * * 16% * * 24% * * 27% 5 * * * 18% * * 27% * * 30% * * * * 650 * * * * * * 10% * * * * ~=HLOOKUP(C1/B1,B4:D9,A1+1,TRUE)*C1 Oops, that 10000% should now be 100%. I played with the formula and didn't change that. This leaves the A1 as a manual input. If you're asking what I think you're asking, you're asking about a circular reference. You want the formula to pick A1 but the formula is dependent on the value in A1. Or? What determines A1? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated If Statement - I Think! | Excel Worksheet Functions | |||
Complicated If Statement...? | Excel Worksheet Functions | |||
Complicated If statement? | Excel Discussion (Misc queries) | |||
Complicated If Then / V Lookup / Match Statement... | Excel Worksheet Functions | |||
semi-complicated nested IF statement | Excel Worksheet Functions |