Formula simplification

[Polanskiman]

Hello,

I need some help in simplifying 3 formulas. Here they are:

=IF(C6<>"Tank Dimensions","",IF(OR(AND(C16="No",D23=""),AND(C16="No",D23<>""),AND(C16="Partially",D23=""),AND(C16="Yes 100%",D23<=""),AND(C16="partially",D19="")),"",IF(AND(C16="Yes 100%",D23=""),"",D13/(D11-($D$12/10))*((D11-($D$12/10))-(D23-($D$12/10))))))

and

=IF(AND(C16="yes 100%",D19<>"",D23<>""),D19-($D$12/10),IF(OR(C6<>"Tank Dimensions",C16="<please select>",AND(C6="tank dimensions",COUNTIF(D9:D12,"")),AND(C16="yes 100%",D23<>"")),0,IF(D19=0,0,D19-($D$12/10))))

and

=IF(AND(C6="tank dimensions",COUNTIF(D9:D12,"")),0,IF(AND(C16="yes 100%",D19<>""),(D19-($D$12/10)),IF(OR(C6="<please select>",C16="<please select>",C16="no",AND(C16="partially",D19=""),AND(C16="yes 100%",D23<>"")),0,IF(OR(D23=0,K36>0),0,(D23-($D$12/10))))))

All these nested IF, AND, OR are driving me crazy. In principle they work as they should yet I can't help to think that there must be a way to simplify them.

I would have attached the worksheet where these 3 formulas are set into context (cell D24, F36 and I36) but it doesn't seem possible to attach files. I tried using the xl2bb Add-in but it doesn't seem to be working as expected. When I preview the post part of the selection made is clipped.

Any help would be appreciated.

 

[RoryA]

AND(C16="No",D23=""),AND(C16="No",D23<>"")

Isn't that just C16="No" in reality?

 

[Fluff]

Cross posted Formula simplification help needed

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Polanskiman]

Cross posted Formula simplification help needed

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Sorry for that. Anyway to add the link now? I don't seem to be able to edit the post anymore.

 

[Fluff]

I've already added the link so need. Just ensure that you supply the link in future.

 

[Polanskiman]

Isn't that just C16="No" in reality?

Indeed! I guess I have spent too much time in front of the formulas that I can't even see the obvious anymore.

 

[Fluff]

The bit in red also seems redundant
=IF(C6<>"Tank Dimensions","",IF(OR(AND(C16="No",D23=""),AND(C16="No",D23<>""),AND(C16="Partially",D23=""),AND(C16="Yes 100%",D23<=""),AND(C16="partially",D19="")),"",IF(AND(C16="Yes 100%",D23=""),"",D13/(D11-($D$12/10))*((D11-($D$12/10))-(D23-($D$12/10))))))

 

[Polanskiman]

The bit in red also seems redundant
=IF(C6<>"Tank Dimensions","",IF(OR(AND(C16="No",D23=""),AND(C16="No",D23<>""),AND(C16="Partially",D23=""),AND(C16="Yes 100%",D23<=""),AND(C16="partially",D19="")),"",IF(AND(C16="Yes 100%",D23=""),"",D13/(D11-($D$12/10))*((D11-($D$12/10))-(D23-($D$12/10))))))

Yes. You are definitely right. Thank you.

So far the formula is looking like this now:

=IF(C6<>"Tank Dimensions","",IF(OR(C16="No",AND(C16="Partially",D23=""),AND(C16="Yes 100%",D23<>""),AND(C16="partially",D19="")),"",D13/(D11-($D$12/10))*((D11-($D$12/10))-(D23-($D$12/10)))))

 

[shaowu459]

the last part:
D13/(D11-($D$12/10))*((D11-($D$12/10))-(D23-($D$12/10)))
equals:
D13/(D11-$D$12/10)*(D11-D23)

 

[Polanskiman]

the last part:
D13/(D11-($D$12/10))*((D11-($D$12/10))-(D23-($D$12/10)))
equals:
D13/(D11-$D$12/10)*(D11-D23)

Indeed. Thank you