Varying values

[Art]

Hi -

I have a problem and I'm not experienced enough to be
able to solve it so any and all assistance will be very
much appreciated.

I need to fill a column of cells with specific dollar
values dependent upon the text value of corresponding
cells in a different column. Example: If H9=A, fill M9
with $30. There are currently 10 different text values
that are used and 3 of them all have the same dollar
value (A, D, P are each worth $30). The other seven
values all have different dollar values. Using an IF for
each one I can get 7 to work but then run into the nested
limitation. Is there a way around this?

The second part of the problem is that I need to have a
fixed dollar amount added to the total whether or not
there is a text trigger for the above. In other words, if
H9=A then M9 should have $30 in it. Should H9 be empty
then M9 should be empty also - UNLESS I9 has a value of 1
which corresponds to a dollar amount of $55. In this
case, assuming that H9 has a text value of A AND I9 has a
value of 1, M9 should reflect a dollar value of $85.
Should it turn out that H9 is empty BUT I9 has a 1 value,
then M9 should reflect a dollar amount of $55.

Any ideas?

Once again, thanks in advance for your help.

Art

 

[CLR]

Make a lookup table listing your text values down one column and their
corresponding number values in the column just to the right and name the
range "Mytable"......

Then, in cell M9, format it for Currency and put this formula:

=IF(AND(H9=0,I9<>1),"",IF(AND(I9=1,H9<>0),55+VLOOKUP(H9,mytable,2,FALSE),IF(
AND(I9=1,H9=0),55,VLOOKUP(H9,mytable,2,FALSE))))

Yucky, but it seems to meet all your criteria........

Vaya con Dios,
Chuck, CABgx3

 

[Norman Harker]

Hi Art!

One way that seems to work is:

=IF(H9="",IF(I9=1,55,""),IF(I9=1,55+VLOOKUP(H9,$AA$1:$AB$16,2,TRUE),VL
OOKUP(H9,$AA$1:$AB$16,2,TRUE)))

I've set up a VLOOKUP table in AA1:AB16.

In AA1:AA16 I have letters A down to P (change to taste)
In AB1:AB16 I have the values to set against those letters.

There are two alternatives if H9 is empty (or a formula that returns
"") and they are 55 if I is 1 and "" if I <> 1.
There are two alternatives if H9 is not empty and they are to extract
the value from the table and either add nothing or add 55 if I9 is 1.

You'll find that most solutions to multiple conditions will be
resolvable with the VLOOKUP function. In this case we are looking up a
text value which is why the final argument in VLOOKUP is TRUE (used
when exact match is required).

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Friday: Guam (Independence Day); Faroe Islands
(Varmakeldustevna); Haiti (Day of Agwe); Latvia (Jewish Genocide
Commemoration); Lesotho (Family Day); Marshall Islands (Fisherman's
Day); Norway (Queen Sonja's Birthday); Philippines (United States
Friendship Day); Puerto Rico (For US Independence Day); Switzerland
(Aarauer Maienzug); Rwanda (Independence Day); Tonga (King's Day);
United States (Independence Day); Virgin Islands (US Independence
Day); Yugoslavia (Freedom Fighter's Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Whooops!

I should say, use FALSE when an exact match is required.

In this case it doesn't make any difference in most cases but where
you are looking up numeric values, it is important to get it right.
Also if you use TRUE you will get the last value in the table if there
looked up value doesn't exist.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Friday: Guam (Independence Day); Faroe Islands
(Varmakeldustevna); Haiti (Day of Agwe); Latvia (Jewish Genocide
Commemoration); Lesotho (Family Day); Marshall Islands (Fisherman’s
Day); Norway (Queen Sonja’s Birthday); Philippines (United States
Friendship Day); Puerto Rico (For US Independence Day); Switzerland
(Aarauer Maienzug); Rwanda (Independence Day); Tonga (King’s Day);
United States (Independence Day); Virgin Islands (US Independence
Day); Yugoslavia (Freedom Fighter’s Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.