worksheet nested if

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

i am trying to write an if statment that will do the following
if c8="p" then y20
if c9="p" then y21
if c8&c9="p" then y20+y21
if c8="c" then nil
if c9="c" then nil

this statment works for the C8 & C9 inputs from strategy sheet 1 at a time
but i am having problems with the sum part
=IF('Strategy Evaluation'!C8="P",Y20*AB20-AD9,IF('Strategy
Evaluation'!C9="P",Y21*AB21-AD9," "))


=IF('Strategy Evaluation'!C8="P",Y20*AB20-AD9,IF('Strategy
Evaluation'!C9="P",Y21*AB21-AD9,IF(AA20=AA21,(Y20*AB20-AD9)+(Y21*AB21-AD9),"
")))

i used something like this but the sum occurs for 2 C's and not 2 P's.
the 2 P's = Y20*AB20-AD9

thanks for any help i am going around in circles
 
Hi Bob!

You haven't covered some possibilities that could cause problems.

Examples:
What do you do if C8 = "p" and C9 = "c"
What do you do if C8 = "c" and C9 = "p"

Try using a decision tree approach and you'll see you have four
possibilities for C8 and C9 ("p", "c", blank and other); You might
treat <> p or c as the same or treat blank cells or "" as being
treated differently to "other" entries.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Thursday 17th July: Iraq (Ba’ath Revolution
day holiday reported by 14-Jul-2003 NYT as cancelled), Israel (Shiva
Asar B’Tammuz), Puerto Rico (Munoz Rivera Day), South Korea
(Constitution Day), US Virgin Islands (Hurricane Supplication Day),
Observances: Fast of 17th Tamuz (Judaism)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top