Data Table with Variables: IF or IF/AND function...

[jyadayada]

Hi Guys, I'll try and explain this as succinctly as possible:

I have 2 chargeable packages, A and B, with costing based on the (STANDING CHARGE * 365)+(RATE*UNITS), however, the standing charge on both package A and B is different if the units are below a thrshold of 700, so on paper looks like this:

Product A:
If UNITS >=700: (0.50*365)+(0.2*UNITS)=OUTPUT
If UNITS <700: (0.65*365)+(0.2*UNITS)=OUTPUT

Product B:
If UNITS >=700: (0.55*365)+(0.25*UNITS)=OUTPUT
If UNITS <700: (0.70*365)+(0.25*UNITS)=OUTPUT

So I want something looking like this;

Select Package: A
Input UNITS: 3,500.00
OUTCOME: 882.5

with Select Package being cell A1, Input UNITS A2 and OUTCOME being A3, with A being B1 but being a drop down list of A or B, 3,500.00 being B2, a manual entry field and 882.5, being the result of the IF / IF/AND statement.

Now if I didn't have the UNITS thrshold bringing a different standing charge into account, and it was just A or B package it would look, something, like this:

=IF(B1="A",(0.50*365)+(0.2*B2),(0.55*365)+(0.25*B2))

I jst can't wrap my head around the if cell B1 is A or B (from the drop down) AND the UNITS are less than 700 then run the second row of equations from the A or B above.

I literally need this to be a very simple tool for the agents or they won't use it, so just select A or B from the drop down in B1, manually enter UNITS in B2 and press enter and the results of the 4 above options appear in B3 (OUTCOME)

I have a sneaky feeling this is going to be one of those kick mysel moments!

thanks in advance!

Jason

[JohnTopley]

Try

=IF(B1="A",(0.2*B2)+IF(B2>=700,0.5*365, 0.65*365),0.25*B2+IF(B2>=700,0.55*365, 0.7*365))

[jyadayada]

That works perfectly, have run numerous variable and all ouputs are bang on, thanks so much!

Just trying to follow th logic, i can kind of see it but it's not fully clicking, you wouldn't have the time / inclination to step through it quickly would you?

thanks again

Jason

[JohnTopley]

IF formula is =IF(Condition, TRUE result, FALSE result)

=IF(B1="A",(0.2*B2)+IF(B2>=700,0.5*365, 0.65*365),0.25*B2+IF(B2>=700,0.55*365, 0.7*365))

If B1="A" then we execute the RED part (TRUE) part of the formula: otherwise we execute the BLUE (FALSE) part.

Taking the TRUE half:

0.2*B2+IF(B2>=700,0.5*365, 0.65*365)

We do the simple multiplication 0.2 * B2 and then the addition using another IF Statement:

so IF B2>=700 (TRUE) we add 0.5*365 otherwise we add 0.65*365

so IF B1="A" AND B2 >=700, we calculate 0.2* B2 + 0.5*365: if B1="A" and B2 <700 we calculate 0.2*B2 +0.65*365

and the same process if B1<> "A" i.e. B1="B"

Hope this helps.