"IF" function

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

Hi
I need to nest 42 value_if_true and value_if_false
arguments - current functionality only allows for 7 - can
anybody help with an alternative?

Txs

Rod
 
Hi Rod
for this i would suggest using the VLOOKUP function. e.g. try the
following
- setup a separate sheet with your lookup criteria (call this for
example 'lookup_sheet'):
A B
1 cond1 value1
2 cond2 value2
....

If the value you want to compare is in cell A1 on a different sheet use
the following function:
=VLOOKUP(A1,'lookup_sheet'!A1:B42,2,0)
 
Hi Rod

depends really what you're trying to achieve but if its something like this
=IF(colour="Red",100,if(colour="blue",125,if(colour="green",130,etc
you can use the VLOOKUP function

put your "tests" down the first column and the "true" element down the next
column e.g.

Red 100
Blue 125
Green 130

and then your vlookup function would look like this

=VLOOKUP(A1,Sheet2!A1:B3,2,false)

where "A1" is the value you want to find in the table, "Sheet2!A1:B3" is the
location of your lookup table, "2" is the column of your lookup table that
stores the value you actually want and where "false" indicates that you are
looking for an exact match.

if this isn't helpful, please post back with a sample (not in an attached
workbook) of your current IF statement.

Cheers
JulieD
 
Two usual alternatives are

1) a lookup table:

J K
1 value1 result1
2 value2 result2
...

then instead of

=IF(A1=value1, result1, if(A1=value2, result2, IF(...

use

=VLOOKUP(A1,J:K,2,FALSE)


2) If your value_if_true's have a mathematical relationship, it's often
possible to use a calculated result, i.e., instead of

=IF(A1<=5,A1*10,IF(=A1<10,A1*20,IF(A1<=15,A1*30...

use

=A1*10*INT(A1/5)
 
What I am trying to do is the following
If a client spends a certain amount of money with us he will receive a discountr based on the spend levels
i.e
FROM TO
250,000 499,000 28.5
500,000 749,000 29.0
750,000 999,000 29.5
1,000,000 1,499,000 30.0
1,500,000 1,999,000 31.0
2,000,000 2,499,000 31.5
2,500,000 2,999,000 32.0
3,000,000 3,499,000 32.5
3,500,000 3,999,000 33.0
4,000,000 4,499,000 33.5
4,500,000 4,999,000 34.0
5,000,000 5,499,000 34.5
5,500,000 5,999,000 35.0
6,000,000 6,499,000 35.5
6,500,000 6,999,000 36.0
7,000,000 7,499,000 36.5
7,500,000 7,999,000 37.0
8,000,000 8,499,000 37.5
8,500,000 8,999,000 38.0
9,000,000 9,499,000 38.5
9,500,000 9,999,000 39.0

etc.. Up to a top amount of 20,000,000. Once the sales reps have calculated the value of an order the spread sheet should automatically apply the correct discount

Thank
Rod
 
Hi Rod
if your amount to test for is in cell D1 use the following
=VLOOKUP(D1,$A$1:$C$100,3)
 
Thanks again to those who helped - just in case there is an easier way to do the calculation this is how I did it -
D53 is the gross cost and the formula was pasted into D54 (nett cost)

=D53*(100%-VLOOKUP(D53,Lookup!J1:L44,3))


EXPOSURE & COST SUMMARY: LIVE & HIGHLIGHTS
Gross Nett
Cost Cost
FINAL TOTAL 600,000.00 426,000.00
Effective Discount -29.0%
 
Back
Top