Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I return a calculation based on several ranges?
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! -- Nanci |
#2
|
|||
|
|||
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. "Nanci" wrote in message ... 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! -- Nanci |
#3
|
|||
|
|||
Nanci
=(G6C66)*(G6<E46)*G46 + (G6C48)*(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 G636" 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 (G6C66) 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: =(G6C66)*(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 =(G6C48)*(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 "Nanci" wrote in message ... 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! -- Nanci |
#4
|
|||
|
|||
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 "Nanci" wrote in message ... 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! -- Nanci |
#5
|
|||
|
|||
Thank you both so much! Now I don't have to look over the bosses shoulder
when he's trying to quote a product! "Steve R" wrote: Nanci =(G6C66)*(G6<E46)*G46 + (G6C48)*(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 G636" 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 (G6C66) 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: =(G6C66)*(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 =(G6C48)*(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 "Nanci" wrote in message ... 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! -- Nanci |
#6
|
|||
|
|||
Hi Nanci
=IF(AND(G6C46,G6<E46),G6*G46,IF(AND(G6C48,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 "Nanci" wrote in message ... 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! -- Nanci |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to return a value based on a whether a number is within a ra. | Excel Worksheet Functions | |||
I want to return "0" based on another cell | Excel Worksheet Functions | |||
Counting an Array based on a calculation | Excel Discussion (Misc queries) | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions |