Software
I have a fixed buying price that needs to be increased by a value according to the fixed amount. ie (<0.5 x2.5) (>0.5 <1 x2.25) (>1 <5 x2.0) (>5 <10 x 1.8) (>10 <20 x1.5) etc.
Without using a highly complex formula, is there an easier way to resolve this?
by xboss
Hi
The simplest way of dealing with this type of problem is to put your variables into a table and then to include a lookup formula in your sheet at the point where you wish to use the variables.
Using the example given you should place the following into a table. Name the table say "table1". Add your further variables as necessary. Note that the table is not out of sync. Read 0 as 0-5, 0.5 as 0.5 to 1, etc.
diff multiplier
0 2.5
0.5 2.25
1 2
5 1.8
10 1.5
Include the followingformula in your sheet at the point you want to use the variables:
=VLOOKUP(???,$table2,2). Replace ??? with the cell reference or calculation of your difference to the fixed price.
Presumably you will use the result of this formula as a multiplier to the price or something.
Hope this helps. If you still have difficulty then look at the help on vlookup.
by xboss
Table should look more like this (I hope)
diff multiplier
0 2.5
0.5 2.25
1 2
5 1.8
10 1.5
Include the following formula in your sheet at the point you want to use thevariables:
=VLOOKUP(???,$table2,2). Replace ??? with the cell reference or calculation of your difference to the fixed price.
Presumably you will use the result of this formula as a multiplier to the price or something.
Hope this helps. If you still have difficulty then look at the help on vlookup.
by ismith
by sette.rar
My Solution uses an Array Formula. Although the Names used are optional, it is a lot easier to understand the formula with them.
first, I made a table from your criteria.
> < Factor
0 .5 2.5
.5 1 2.25
1 5 2
5 10 1.8
10 20 1.5
I named the 1st column of numbers LOW,
the second HIGH, and the third FACTOR
When you enter this formula use Cntl-Shift-Enter
=IF(LOWHIGH,Test*FACTOR,0)
Again, Test can be a named cell or any cell Ref.
It is easy to change the numbers, and if you use names you can use the formula on other sheets, or hide the sheet with the criteria.
by sette.rar
If you want to take it one step farther, you can make everything into names,
including the formula.
Go to Insert -- Name -- Define
Type Low as the Name
as the Reference type this ={0,0.5,1,5,10}
Then click add.
once again for High ={0.5,1,5,10,20}
and for Factor ={2.5,2.25,2,1.8,1.5}
Now you can also add a named formula
put MyCalc or something in the name area
and for the reference type
=IF(LowHigh,A2*Factor,0)
and use Cntrl Shift Enter
You can use =MyCalc as normal in cells, you can copy it to different cells and Excel works it's magic as usual. In this case it expects the input cell to be the cell imediately to the left of the formula cell.
This discussion is closed to new replies.
Sort by Trending