Nesting IF?

[Szucchari]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Billable / Non-Billable
[/TD]
[TD]Electronic / Mail
[/TD]
[TD]Page Cost
[/TD]
[TD]search Fee Y/N
[/TD]
[TD]Search Fee
[/TD]
[TD]PDF
[/TD]
[TD]Postage
[/TD]
[TD]Total Billed
[/TD]
[/TR]
[TR]
[TD]Billable
[/TD]
[TD]Mail
[/TD]
[TD]$$
[/TD]
[TD]Y
[/TD]
[TD]$10.00
[/TD]
[TD]$6.50
[/TD]
[TD]$$
[/TD]
[TD]=$$
[/TD]
[/TR]
[TR]
[TD]Billable
[/TD]
[TD]Electronic
[/TD]
[TD] -
[/TD]
[TD]Y
[/TD]
[TD]$10.00
[/TD]
[TD]$6.50
[/TD]
[TD]-
[/TD]
[TD]=$$
[/TD]
[/TR]
[TR]
[TD]Non - Billable
[/TD]
[TD]Mail or electronic
[/TD]
[TD]0
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD][/TD]
[TD]=$0
[/TD]
[/TR]
[TR]
[TD]Non- Billable
[/TD]
[TD]Mail
[/TD]
[TD]0
[/TD]
[TD]Y
[/TD]
[TD]10.00
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]$10.00
[/TD]
[/TR]
</tbody>[/TABLE]
Hi All,

Please let me know if I am thinking correctly or if I am totally wrong. below is what I am trying to make happen and I am with the limited excel knowledge I have, I am thinking my only logical way is to do a nesting IF but this will most likely be the longest IF in history lol and I am not sure if I can pull it off or if it will even work.

If A2 is billable and B2 is Mail and D2 is Y , then I need H2 = C2+E2+G2
If A2 is Billable and B2 is electronic and D2 is Y, Then I need H2 = E2 + F2
If A2 is Non-Billable and D2 is N, then H2 = 0
If A2 is Non-Billable and D2 is Y, then H2 = 10


Please advise

 

[kweaver]

Phew! Maybe:

=IF(AND(A2="billable",NOT(ISERROR(FIND("Mail",B2))),D2="y"),C2+E2+G2,IF(AND(A2="billable",NOT(ISERROR(FIND("Electronic",B2))),D2="y"),E2+F2,IF(AND(A2="non-billable",D2="n"),0,10)))

NOTE: The FIND function is case-sensitive, so Mail and Electronic have to have 1st letter in upper-case in the B column or change the IF statement.

 

[Eric W]

You seem to have left out a lot of cases. For example, is "Mail or Electronic" a valid code, or just an indication of both possibilities? But if I understand the underlying rules correctly, you might get away with something like this:

=IF(A2="Billable",(D2="Y")*E2+IF(B2="Mail",C2+G2,F2),0)

 

[Szucchari]

Thank you!!! I never would have been able to come up with that!