How do I return a calculation based on several ranges?

[Guest]

How (or can) I enter a formula that would do the following:

if G6 is >C46 and <E46 then multiply by G46, but if G6 is >C48 and <E48
then multiply by G48. I have five sets of ranges to include and five
different amounts to multiply by based on the number in G6. Example, if G6
is between 800 and 2400 then multiply it by .254, if it's between 2401 and
5000 then multiply it by .234 and so on.

Thanks!

 

[Dave R.]

For this type of problem, you can either use a bunch of IF statements, or
use a lookup function similar to this;

=A1*LOOKUP(299,{0,2;300,3;500,55})

The 0,2 or 300,3 or 500,55 indicate the "lookup range" and the "returned
value".

The 0 is the low end of the range (could be negative if you want) and 500 is
the minimum cutoff to return 55.

You would substitute ranges 800, 2401, and so on. The 299 in the above
example is the number to be looked up.

 

[Steve R]

Nanci

=(G6>C66)*(G6<E46)*G46 + (G6>C48)*(G6<E48)*G48


Let me try to explain:

You could use If() to construct a complex formula. You could also use the
fact that a boolean Result of true is equated as 1, whilst a boolean result
of false equates as 0.

The first part of you complex formula "If G6>36" multiplied by 1 will return
1 if True or 0 if false.
To check that a formula meets each of two sets of conditions is simply a
matter of multiplying the result of each side of the equation:
Let's assume (G6>C66) is true and (G6<E46) is false.

The True side of the equation returns 1 and the false side returns 0. 1*0 =
0

Applying that logic to the first part of your equation:

If both sides are true:
=(G6>C66)*(G6<E46)*G46
equates to
= 1 * 1 *G46

Applying the same logic to the 2nd half of the formula if G6 is not greater
than C48

=(G6>C48)*(G6<E48)*G48
= 0 * 1 *G48

Now if you add both parts together, the return value will be the value in
G46

Hoping my explanation is understandable,
Steve

 

[CLR]

I would use a RangeNamed table for my ranges and multipliers, named
"MultiplyBy" and a
VLOOKUP formula to do the work...........

=G6*VLOOKUP(G6,MultiplyBy,2,TRUE)

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thank you both so much! Now I don't have to look over the bosses shoulder
when he's trying to quote a product!

 

[JulieD]

Hi Nanci

=IF(AND(G6>C46,G6<E46),G6*G46,IF(AND(G6>C48,G6<E48),G6*G48,0))
you can put more IF statements in where the 0 is using the following
structure
=IF(AND(Test1,test2),TRUE,IF(AND(test1,test2),TRUE,IF(AND(test1,test2),TRUE,IF(AND(test1,test2),TRUE,IF(And(test1,test2),TRUE,FALSE)))))

Cheers
JulieD