A lot of IF's

[sallan]

Hi, I am new to excel formulas. I need to do the following:


If E2 is 1 then h2 = 25


If E2 is more than 1 but less than 10 H2 will increment by 25 for every digit increase in E2


If E2 is more than 10 but less than 50 H2 = will increment by 18.75 for every digit increase in E2


If E2 is more than 50 but less than 100 H2 will increment by 12.5 for every digit increase in E2


If E2 is more than 100 but less than 250 H2 will increment by 7.5 for every digit increase in E2

 

[jason]

just to make sure i understand the conditions... can you provide a ocuple samples and what the value returned should be?

 

[sallan]

Hi Jason


this appears to work for the first two conditions:


=IF(E2=1,25,IF(E2<4,100,(100+((E2-4)*25))))


Hope that answers your question


Sam

 

[jason]

...where did the 'if <4 then 100' come from?


is 0 ever a possibility?


you may want to edit this so that values equal to any of the criteria will still be counted. So if E2=100, your current criteria would return false....


=IF(E2=1,25,IF(AND(E2>1,E2<10),E2*25,IF(AND(E2>10,E2<50),E2*18.75,IF(AND(E2>50,E2<100),E2*12.5,IF(AND(E2>100,E2<250),E2*7.5)))))

 

[Debraj]

Hi Sallan,


Welcome to the forum..


Please create a separate area for your data distinguish.. as below..

[pre]

Threshold	Price	Price Difference	E2			H2
0		25	25			51			1012.5
10		18.75	-6.25
50		12.5	-6.25
100		7.5	-5
250		0	-7.5[/pre]
Now in H2 write formula as 


=SUMPRODUCT((E2>=$A$2:$A$6)*(E2-$A$2:$A$6)*($C$2:$C$6))

BTW.. I am welcoming you.. by one of my favourite formula from this site..

Courtesy : Kyle McGhee..


Regards,

Deb

 

[sallan]

Hi Jason


...where did the 'if <4 then 100' come from? Sorry I used this when I was considering a change threshold of 5, the rest is for the same reason


is 0 ever a possibility? No

 

[jason]

see my 'crack at it' above and let me know

 

[imgonna]

Hi Sallan


I got lost on what you meant / logic -- could you provide your expected output when E2 = 10, 20, 50, 100. However, I did this via a udf:


Public Function H2Calc(e2val As Single) As Single


Dim h2mult As Single


Select Case e2val


Case Is = 1

h2mult = 25

H2Calc = h2mult

Case Is > 250

htmult = 6.5

H2Calc = e2val + ((e2val - 250) * htmult)

Case Is > 100

htmult = 7.5

H2Calc = e2val + ((e2val - 100) * htmult)

Case Is > 50

htmult = 12.75

H2Calc = e2val + ((e2val - 50) * htmult)

Case Is > 10

htmult = 18.75

H2Calc = e2val + ((e2val - 10) * htmult)

Case Is > 1

htmult = 25

H2Calc = e2val + ((e2val - 1) * htmult)


End Select


End Function


Use H2Calc() as the formula and E as in the input.


Not sure I got the math right --


1 25

10 235

20 207.5

50 800

100 737.5


Cheers

G

 

[sallan]

Hi Jason


giant sized improvement


This works for all numbers except the threshold numbers - 10, 50, 100 & 250 or above give a false return.


Sam

 

[imgonna]

Hi Sam


On the basis of you last post, my formula is wrong and it's more simple. Modify all the H2CALC formula to read: H2Calc = e2val * htmult


Should be fine then.


Glen

 

[Debraj]

Hi Sam,


My previous formula was little bit smaller.. I hope you missed that one.. So AGAIN.. doing the same with little bit larger formula..

[pre]

Threshold	Price			E2			H2
1		25			51			1012.5
11		18.75
51		12.5
101		7.5
251		0[/pre]

Now in H2 write formula as


=SUMPRODUCT(LOOKUP(ROW(INDIRECT("A1:A"&E2)),$A$2:$A$6,$B$2:$B$6))

Regards,

Deb

 

[Faseeh]

Hi sallan,


Try this:

=LOOKUP(E2,{1,10,50,100,251},{25,18.75,12.5,7.5})

For your threshold, you have included them in either of the steps, that is creating a problem. any ways have a try.


Regards,

 

[jason]

=IF(E2=1,25,IF(AND(E2>1,E2=<10),E2*25,IF(AND(E2>10,E2=<50),E2*18.75,IF(AND(E2>50,E2=<100),E2*12.5,IF(AND(E2>100,E2=<250),E2*7.5)))))


there...that should include the threshold numbers

 

[sallan]

Hi Jason


What a star you are, I was worried about how to make this work over the weekend.


A big, big thank you.


Sam

 

[SirJB7]

@SirJB7

Hi, myself!

So long...

I prefer Fasseh formula just tweaked a little:

=IFERROR(LOOKUP(E2,{1,10,50,100,251},{25,18.75,12.5,7.5}),"err_msg")

It's neater, cleaner, simpler, nicer, ...er, ...

Regards!