multi-IF-function?
Posted by Jan on June 26, 2001 3:31 AM
Hello out there,
I am trying to assign to a column of data a certain multiplyer:
Lets say in column "A" I have various numbers varying between 1 and 9.
In colum "B" I have some amounts of Money.
My aim is to multiply the value in a cell in column "B" with a certain constant that depends on the number, which stands in the adjacent cell(in column "A").
If I have only two numbers I can use a "If_Then" function. But as soon as I have more than two it does not work any more.
Does anybody know how to solve that problem?
Thanks in advance
Jan
Posted by Aladin Akyurek on June 26, 2001 4:08 AM
VLOOKUP often when multiple or nested IF's needed
Jan
You didn't tell how many multipliers you have and how these are related to numbers in A. So I'm cooking up an example.
Lets call a number in A just X, lets call a multiplier M.
Lets take the following values as example.
X=1, M=0.4
X=2, M=0.5
X=3, M=0.6
X=4, M=0.7
X=5, M=0.8
X=6, M=0.9
X=7, M=1.0
X=8, M=1.5
X=9, M=2.0
The values of M need not be ascending.
Use the following formula in C1
=B1*VLOOKUP(A1,{1,0.4;2,0.5;3,0.6;4,0.7;5,0.8;6,0.9;7,1;8,1.5;9,2},0)
Copy this down as far as needed.
Aladin
==============
Posted by Phil on June 26, 2001 4:13 AM
use a vlookup to get the value of the multiplier
e.g (in C1)
=vlookup(A1,Data,2,0)*B1
You would then have to make a Data range called "data" - with the 1stcollumn having the 0-9
and in the next collumn along have the value you want to multiply by.
Email me if you get stuck as it's only a 5 seconds thing
HTH
Phil
Posted by Takahashi on June 26, 2001 10:22 AM
Re: VLOOKUP often when multiple or nested IF's needed
Posted by Takahashi on June 26, 2001 10:24 AM
Re: VLOOKUP often when multiple or nested IF's needed
Maybe I do not clearly understand what need...but if you are trying to calculate some kind of weighted average, this formula works:
{=SUMPRODUCT(A1:A9,B1:B9)}
Tak.
**************
