Math within a formula.

[Oteshatori]

Let's say in B12 I have a number between 1 and 4+.
Now, let's say in cell B16 I want something like: if B12 = 1 then $0, if B12 = 2 then 2*$10, if B12 = 3 then 3*$15, if B12 = 4 then 4*$20, if B12 = greater than 4 then that number *$20.

My head hurts now, but that's the idea. Any thoughts on making such a whacky thing happen?

 

[tyija1995]

Hey,

perhaps something like

IF(B12<1,0,SWITCH(B12,1,0,2,20,3,45,4,80,B12*20))

 

[kweaver]

Well, one way would be:

=IF(B12=1,0,IF(B12>=4,B12*20,10+5*(B12-2)))

 

[Oteshatori]

With this one: =IF(B12=1,0,IF(B12>=4,B12*20,10+5*(B12-2)))

Entering 1 came back with $0 in B16 which is correct.
Entering 2 came back with $10, but it needs to be $10*2 with a return of $20
Entering 3 like above
Entering 4+ came back with the correct answer.

How can we fix 2 and 3?

 

[Oteshatori]

Entering =IF(B12<1,0,SWITCH(B12,1,0,2,20,3,45,4,80,B12*20))

Came back with

 

[jtakw]

Hi,

Here's another way:

Book3.xlsx
	B	C	D
12	1		0
13	2		20
14	3		45
15	4		80
16	5		100
17	99		1980
Sheet780

Cell Formulas
Range	Formula
D12:D17	D12	=B12*IFERROR(CHOOSE(B12,0,10,15),20)

EDIT: Just made my formula a bit shorter.

 

[kweaver]

=IF(B12=1,0,IF(B12>=4,B12*20,B12*(10+5*(B12-2))))

PS. You should change your profile to show what version of Excel you're running. SWITCH is available in 365, but if you're running another version you won't have it.

 

[jtakw]

Hi,

Adding another option:

Book3.xlsx
	B	C	D	E
12	1		0	0
13	2		20	20
14	3		45	45
15	4		80	80
16	5		100	100
17	99		1980	1980
Sheet780

Cell Formulas
Range	Formula
D12:D17	D12	=B12*IFERROR(CHOOSE(B12,0,10,15),20)
E12:E17	E12	=B12*LOOKUP(B12,{1,2,3,4},{0,10,15,20})

 

[Oteshatori]

kweaver, you're right I'm on Excel 2016 now.
jtakw, nice approach! I need to be able to put whatever number in B12 for the result in B16, however.

 

[jtakw]

jtakw, nice approach! I need to be able to put whatever number in B12 for the result in B16, however.

So just put the formula in B16...my Table is just a sample...