stuck with Excel If function

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

I am stuck trying to figure out a formula to put into
cell A20 that will do the following:

If cell A4 is blank then cell A20= blank,
if cell A4 is >=0 and cell A2 = "Sat", cell A20 = 160
if cell A4 is >=0 and cell A2 = "sun", cell A20 = 160
if cell A4 is >=0 and cell A2 = "Mon", cell A20 = 200
if cell A4 is >=0 and cell A2 = "Tue", cell A20 = 200 etc
etc through "Fri". In some cases I have multiple cells
adding up eg. if A1+A2+A3>+0 and cell B2 = "Sat" etc.
don't know if that makes a difference or not.

I have been working on this for days and just can't seem
to get it. Any help would sure be appreciated.
 
Hi Ken
try the following:
1. Set-up a lookup table for matching the strings with a numer. Lets
say on a separate sheet called 'lookup' you enter something like the
following:
A B
1 Sat 160
2 Sun 160
3 Mon 200
......

2. Now use the following formula in A20:
=IF(A4="","",IF(A4>=0,VLOOKUP(A2,'lookup'!$A$1:$B$20,2,0),"not
defined"))

Note: This does not cover your criteria like A1+A2+A3>0 and the
B2="Sat" condition. for this you may provide some more information
 
Back
Top