Help with too many variables!

lwiss

New Member
Joined
Sep 9, 2015
Messages
7
I'm having a bit of trouble creating a formula with too many variables, basically I need up to 5 variables.

If K19 is between 1-10 then add c47*28
If k19 is between 11-20 then add c47*20
If K19 is between 21-30 then add c47*10
If k19 is between 31-40 then add c47*5
if k19 is equal to/greater than 41 then = C47*0

I had:

=IF(AND(Sales!K19>=1,Sales!K19<=10),(Sales!C47*28), IF(AND(Sales!K19>=11,Sales!K19<=20),(Sales!C47*20), IF(AND(Sales!K19>=21,Sales!K19<=30),(Sales!C47*10),(C47*10))))

But it won't let me do more than 3 functions.

Any help is appreciated for a novice!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hi. Im not sure what you mean by it only lets you do three functions. Your formula would be (you will have to put the sheet refs in):

=IF(AND(K19>=1,K19<=10),(C47*28),IF(AND(K19>=11,K19<=20),(C47*20),IF(AND(K19>=21,K19<=30),(C47*10),IF(AND(K19>=31,K19<=40),(C47*5),0))))

or you could use this:

=LOOKUP(K19,{1,11,21,31,41},{28,20,10,5,0})*C47
 
Upvote 0
Welcome to the forum.

Try

=C47*LOOKUP(K19,{0,1,11,21,31,41},{0,28,20,10,5,0})
 
Upvote 0
I tried =C47*LOOKUP(K19,{0,1,11,21,31,41},{0,28,20,10,5,0}) and =IF(AND(K19>=1,K19<=10),(C47*28),IF(AND(K19>=11,K19<=20),(C47*20),IF(AND(K19>=21,K19<=30),(C47*10),IF(AND(K19>=31,K19<=40),(C47*5),0)))) with the Sales! sheet reference included and now it's returning no values at all, not sure where to go from there
 
Upvote 0
The lookup should look like

=Sales!C47*LOOKUP(Sales!K19,{0,1,11,21,31,41},{0,28,20,10,5,0})
 
Upvote 0
One more quick one! If I wanted to make a similar formula, but instead of working for one cell I wanted the result of Sales!D6 to go to D17, Sales!D7 to add to that, Sales!D8 to add to that etc. What changes would I need to make?
 
Upvote 0
So if Sales!D6 = 85-119 I want to add 14 to D17, if it's 55-85 then add 9 etc., then the same for Sales!D7 , Sales!D8 etc. :S Not sure if that makes more sense
 
Upvote 0

Forum statistics

Threads
1,223,719
Messages
6,174,089
Members
452,542
Latest member
Bricklin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top