Help with too many variables!

[lwiss]

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!

 

[gaz_chops]

What is in D17?

 

[lwiss]

The total of Sales!d7 + Sales!d8 etc. There values are determined by 0-44.99=0, 45-54.99=1, 55-74.99=5, 75-118.99=9, Anything above 119=14.

So Basically

If Sales!D7 is 119 then D17 = 14
If Sales!D8 is 90 then D17 = 23
If Sales!D9 is 119 then D17=37


Etc.

 

[gaz_chops]

Try

=LOOKUP(D6,{0,45,55,75,119},{0,1,5,9,14})+LOOKUP(D7,{0,45,55,75,119},{0,1,5,9,14})+LOOKUP(D8,{0,45,55,75,119},{0,1,5,9,14})+LOOKUP(D9,{0,45,55,75,119},{0,1,5,9,14})

That covers D6:D9, if you need more copy and paste +LOOKUP(D9,{0,45,55,75,119},{0,1,5,9,14}) to the end and change the D9 to the new cell reference.

 

[lwiss]

Again, brilliant. Thanks very much

 

[gaz_chops]

You're welcome.