Nested IF and ISBLANK formula needs work...

[M15tyw00d]

Ok the boxes in red need to be cleared if there is an amount in the blue boxes. Please help. Been trying to find an answer to this since the 19th...

Code for Red box is
=IF(ISBLANK(B4),"",IF(A4="I",130,IF(A4="T",100,IF(A4="S",75,IF(E4="<>0","")))))

Code for the Blue box is
=IF(D4="D",C4*0.8,IF(D4="SD",C4-40,""))

So you can see that the criteria for there being a number in blue is based on there being a discount code in the Orange box.

And a number shows in the Red box based on the Age code in Green box.

<table width="363" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 80pt;" width="107"> <col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15.75pt;" align="center" height="21"> <td colspan="5" class="xl65" style="height: 15.75pt; width: 272pt;" width="363" height="21">Day</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">Age</td> <td align="center">Name</td> <td align="center">Price</td> <td align="center">Discount</td> <td align="center">New Cost</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">I</td> <td class="xl64" align="center">Jacob Robinson</td> <td class="xl69" align="center">$130</td> <td class="xl66" align="center">SD</td> <td class="xl68" align="center">$90</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">T</td> <td class="xl64" align="center">Brock Robinson</td> <td class="xl69" style="border-top: medium none;" align="center">$100</td> <td class="xl66" align="center">SD</td> <td class="xl68" align="center">$60</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">T</td> <td class="xl64" align="center">
</td> <td class="xl68" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl68" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">T</td> <td class="xl64" align="center">
</td> <td class="xl68" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl68" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">T</td> <td class="xl64" align="center">
</td> <td class="xl68" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl68" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">T</td> <td class="xl64" align="center">
</td> <td class="xl68" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl68" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">S</td> <td class="xl64" align="center">
</td> <td class="xl68" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl68" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">S</td> <td class="xl64" align="center">
</td> <td class="xl68" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl68" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">S</td> <td class="xl64" align="center">
</td> <td class="xl68" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl68" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt;" align="center" height="21">S</td> <td class="xl64" align="center">
</td> <td class="xl68" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl68" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" align="center" height="20">
</td> <td class="xl67" align="center">Week</td> <td class="xl68" align="center">$380</td> <td align="center">
</td> <td align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td colspan="2" class="xl67" style="height: 15pt;" align="center" height="20">Approximately Month</td> <td class="xl68" align="center">$1,520</td> <td align="center">
</td> <td align="center">
</td> </tr> </tbody></table>

 

[M15tyw00d]

Please help if you can.

 

[jlatendre]

As far as I know you will haveto create another box in order to do that because you have blue box depending on red box, but the red box depends on the blue one, so its like a vicious circle. But I could be wrong, maybe some of the pros here could correct me if im wrong?
Jesse

 

[M15tyw00d]

The Blue box depends on the Green box. If there is a discount code like the "SD" in the Orange area then the Blue box will take the information from the red box and subtract $40. If there is nothing in the orange area then it will return a blank box.

The red box will look to the name box and if it is blank will return a blank box. If there is a name there then it will look to the green box and read the Age code and give an amount based on that code.

I also want that code to read the blue area or the orange area. If there is a code in the Discount orange area or an amount in the blue area then I want the amount in the red box to delete or show an invisible $0.

Maybe it should read the orange box. That way there is no loop. How do I do that...

 

[M15tyw00d]

iF i DO SOMETHING LIKE

=IF(ISBLANK(B4),"",IF(A4="I",130,IF(A4="T",100,IF(A4="S",75,IF(ISBLANK(D4), ,"")))))

it does not work.

Basically if the orange box (D4) has SD or D in it then this box the red (C4) should return a blank cell.

 

[jlatendre]

I think I follow you now, try this
=IF(OR(D4="D",D4="SD"),"",IF(ISBLANK(B4),"",IF(A4="I",130,IF(A4="T",100,IF(A4="S",75,IF(E4="<>0",""))))))

Jesse

 

[M15tyw00d]

That worked perfect though it did loop so I had to add a column.

It looks like this
I will hide the purple column

Purple column code
=IF(ISBLANK(B4),"",IF(A4="I",130,IF(A4="T",100,IF(A4="S",75,))))

Red column code
=IF(OR(E4="D",E4="SD"),"",IF(ISBLANK(B4),"",IF(A4="I",130,IF(A4="T",100,IF(A4="S",75,)))))

Green Column code
=IF(E4="D",D4*0.8,IF(E4="SD",D4-40,""))

Thank you for that OR statement. That was the KEY!!! AWESOME!

<table width="427" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 80pt;" width="107"> <col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 15.75pt;" align="center" height="21"> <td colspan="6" class="xl66" style="height: 15.75pt; width: 320pt;" width="427" height="21">Day</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl66" style="height: 15.75pt;" align="center" height="21">
</td> <td class="xl66" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl66" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">Age</td> <td align="center">Name</td> <td align="center">Price</td> <td align="center">
</td> <td align="center">Discount</td> <td align="center">New Cost</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">I</td> <td class="xl65" align="center">Jacob Robinson</td> <td class="xl69" align="center">
</td> <td class="xl69" align="center">$130</td> <td class="xl67" align="center">SD</td> <td class="xl69" align="center">$90</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">T</td> <td class="xl65" align="center">Brock Robinson</td> <td class="xl69" align="center">
</td> <td class="xl69" align="center">$100</td> <td class="xl67" align="center">SD</td> <td class="xl69" align="center">$60</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">T</td> <td class="xl65" align="center">
</td> <td class="xl69" align="center">
</td> <td class="xl69" align="center">
</td> <td class="xl67" align="center">
</td> <td class="xl69" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">T</td> <td class="xl65" align="center">
</td> <td class="xl69" align="center">
</td> <td class="xl69" align="center">
</td> <td class="xl67" align="center">
</td> <td class="xl69" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">T</td> <td class="xl65" align="center">Christopher</td> <td class="xl69" align="center">
</td> <td class="xl69" align="center">$100</td> <td class="xl67" align="center">D</td> <td class="xl69" align="center">$80</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">T</td> <td class="xl65" align="center">
</td> <td class="xl69" align="center">
</td> <td class="xl69" align="center">
</td> <td class="xl67" align="center">
</td> <td class="xl69" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">S</td> <td class="xl65" align="center">Chylind</td> <td class="xl69" align="center">$75</td> <td class="xl69" align="center">$75</td> <td class="xl67" align="center">
</td> <td class="xl69" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">S</td> <td class="xl65" align="center">Adrian</td> <td class="xl69" align="center">$75</td> <td class="xl69" align="center">$75</td> <td class="xl67" align="center">
</td> <td class="xl69" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">S</td> <td class="xl65" align="center">
</td> <td class="xl69" align="center">
</td> <td class="xl69" align="center">
</td> <td class="xl67" align="center">
</td> <td class="xl69" align="center">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" align="center" height="21">S</td> <td class="xl65" align="center">
</td> <td class="xl69" align="center">
</td> <td class="xl69" align="center">
</td> <td class="xl66" align="center">
</td> <td class="xl69" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="center" height="20">
</td> <td class="xl68" align="center">Week</td> <td class="xl69" align="center">$380</td> <td class="xl69" align="center">
</td> <td align="center">
</td> <td align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td colspan="2" class="xl68" style="height: 15pt;" align="center" height="20">Approximately Month</td> <td class="xl69" align="center">$1,520</td> <td class="xl69" align="center">
</td> <td align="center">
</td> <td align="center">
</td> </tr> </tbody></table>

 

[jlatendre]

So everything works good? Thats what really matters, or do you need more help?
Jesse

 

[M15tyw00d]

It works perfect now! Thank you so much!