Create a formula with conditions

[Guest]

How can I create a formula for the following:

if A2 is <= 25 then * D8 by B4, and if A2 is <=25 but >=50 then *D8 by B5,
and if A2 is<=50 but >=100 then * D8 by B6?

So essentially if A2 is 0-25 then multiply D8 (a constant) by .10
and if A2 is 25-50, then multiply D8 by .2
and if A2 is 50-100, then multiply D8 by .4
and if A2 is 100-200, then multiply D8 by .5

Thanks for any help.

JP

 

[Pete_UK]

Here's one way:

=D8*IF(A2<=25,0.1,IF(A2<=50,0.2,IF(A2<=100,0.4,0.5)))

If you decide to have more conditions, then you might consider a
lookup table.

You can use B4, B5, B6 etc in the formula instead of the actual values
I have posted.

Hope this helps.

Pete

 

[Myrna Larson]

You are writing <= when you mean >= and vice versa. I hope that isn't spilling
over into your formulas...

=IF(A1>=100,D8*.5,IF(A1>=50,D8*.4,IF(A2>=25,D8*.2,IF(A2>=0,D8*.1))))

You say 100-200, but the above will multiply D8 by 0.5 if A2 is > 200. Is that
possible?

There's some confusion in your verbal description re the boundary values. You
say 25-50, and then 50-100. I've assumed that 50 belongs in the 2nd category
rather than the 1st. If that's not the case, change all of the >= to >

Another possibility is

=D8*CHOOSE(A2/25+1,.1, .2, .4, .4, .5)

It's using these ranges, 0-24.99, 25-49.99, 50-74.99, 75-100, > 100, where the
multiplier for the 3rd and 4th ranges is the same.

You could also put your bin boundaries and multipliers in table elsewhere on
the sheet and use VLOOKUP, but that may be overkill for just 4 categories.

 

[Ron Coderre]

Your phrasing is a bit ambiguous...

If your ranges are actually:
A2 >=0 AND A2 <=25
A2 > 25 AND A2 <=50
A2 > 50 AND A2 <=100
A2 > 100

and...
B4: 0.1
B5: 0.2
B6: 0.4
B7: 0.5

Then try this:
=D8*INDEX(B4:B7,MATCH(1,FREQUENCY(A2,25*{1,2,4}),0))

Does that help?
If no, can you clarify the specifications?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Ron Coderre]

Another possibility:
=D8*INDEX(B4:B7,SUM(--(A2>{0,25,50,100})))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Thank you so much for your response. I have a few questions and would like
to changes the ranges. M22 will hold the range values, and K24 will hold the
constant.

So how does this look
=IF(M22>=200,K24*K27,IF(M22>=100,K24*I27,IF(M22>=50,K27*H27,IF(M22>=25,K27*G27,IF(M22<25,K27*0)))))


New Ranges:
0-24.99 *0
25-49.99 *G27 (.1)
50-99.99 *H27 (.2)
100-199.99 *I27 (.4)
(Greater than) >200 *K27 (.5) Did I use the > correctly?

 

[Ron Coderre]

Try this:

=K24*CHOOSE(SUM(--(M22>={0,25,50,100,200})),0,G27,H27,I27,K27)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)