Hi all,
Last week I was completely stuck with this concept and Kweaver helped me out with this awesome formula, but since then I needed to modify the formula to add another condition and now I cannot get the formula to make S2 = 0 is the other conditions are "Non-Billable" and "No". Below is what I need the formula to do and the formula I am trying to use. The issue I am having is with the last IF in the formula. atleast I think that is where it is broken.
<colgroup><col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;">
<col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;">
<col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;">
<col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;">
<col width="64" style="width: 48pt;" span="8">
<tbody>
</tbody>
=IF(AND(E3="Billable",NOT(ISERROR(FIND("Mail",I3))),R3="Yes"),Q3+S3+$AC$18,IF(AND(E3="Billable",NOT(ISERROR(FIND("Electronic",I3))),R3="Yes"),$AC$18+$AC$19,IF(AND(E3="Billable",NOT(ISERROR(FIND("CD",H3))),R3="Yes"),$AC$18+$AC$19+S3,IF(AND(E3="Non-billable",R3="No"),0,10))))
Last week I was completely stuck with this concept and Kweaver helped me out with this awesome formula, but since then I needed to modify the formula to add another condition and now I cannot get the formula to make S2 = 0 is the other conditions are "Non-Billable" and "No". Below is what I need the formula to do and the formula I am trying to use. The issue I am having is with the last IF in the formula. atleast I think that is where it is broken.
D | G | H | P | Q | R | S | AA | AB | |||
Billable / Non - Billable | Shipment Type | Electronic / mail | Page cost | Search Fee Yes /No | Postage | Total Billed amount | Yes | ||||
3 | #N/A | #N/A | $ 100.00 | 0 | $10.00 | No | |||||
4 | #N/A | #N/A | $ 100.00 | $10.00 | Search Fee | $ 10.00 | |||||
5 | #N/A | #N/A | $ 100.00 | $10.00 | Electronic | $ 6.50 | |||||
If D3 is Billable and H3 is Mail and Q3 is Yes , then I need S3= P3+R3+$AB$16 | |||||||||||
If D3 is Billable and H3 is Electronic and Q3 is Yes , then I need S3= $AB$17+$AB$16 | |||||||||||
If D3 is Billable and G3 is CD and Q3 is Yes , then I need S3= $AB$17+$AB$16+R3 | |||||||||||
If D3 is Non-Billable and Q3 is No, then S3 = 0 | |||||||||||
If D3 is Non-Billable and Q3 is Yes, then S3 = 10 |
=IF(AND(E3="Billable",NOT(ISERROR(FIND("Mail",I3))),R3="Yes"),Q3+S3+$AC$18,IF(AND(E3="Billable",NOT(ISERROR(FIND("Electronic",I3))),R3="Yes"),$AC$18+$AC$19,IF(AND(E3="Billable",NOT(ISERROR(FIND("CD",H3))),R3="Yes"),$AC$18+$AC$19+S3,IF(AND(E3="Non-billable",R3="No"),0,10))))