IF/and formula

[msbing916]

I have an IF/and formula set up to show calculated payments based off the equipment cost and linked to check boxes so when the boxes were checked, it showed the payment that corresponded. If no boxes were checked, no value showed up. Originally it was just an IF formula and it worked great. I had to add an extra condition to the formula which is why the formula is now an if/and formula. With the extra condition added, those cells always show a value whether the check box is checked or not. Any ideas how to fix this?

Here is a sample of the formula:
=(IF(AND($A18>0,B$17>0,$H$20=2),IF(Inputs!$B$8>=75000,Inputs!$B$8*Inputs!$E79,IF(Inputs!$B$8>=50000,Inputs!$B$8*Inputs!$D79,IF(Inputs!$B$8>=15000,Inputs!$B$8*$C79,Inputs!$B$8*Inputs!$B79))),IF(Inputs!$B$8>=75000,Inputs!$B$8*Inputs!$O79,IF(Inputs!$B$8>=50000,Inputs!$B$8*Inputs!$N79,IF(Inputs!$B$8>=15000,Inputs!$B$8*$M79,Inputs!$B$8*Inputs!$L79)))))

The check boxes put the values in cells A18, B17. H20 is set up as a radio button so there is always a value present.

The earlier formula that worked was:
=IF(AND($A18>0,B$17>0),IF(Inputs!$B$8<15000,Inputs!$B$8*Inputs!$B71,IF(Inputs!$B$8>49999.99,Inputs!$B$8*Inputs!$D71,Inputs!$B$8*Inputs!$C71))," ") (it is missing the logic for cell H20)

I am pretty sure it is not working because h20 is set up as a radio button. For the purposes of the spreadsheet, I need to keep it this way so is there an addition I can make to the formula to account for this?

Any help would be much appreciated!!

[msbing916]

still looking for advice for this!

THanks.

[EdMac]

You have your syntax all wrong.

Your IF runs out halfway through where you close off 3 sets of brackets - the last half of the formula is nor doing anything.

Without knowing what you are trying to achieve it's difficult to advise - suggest you post a copy of your sheet if you can (ZIP it then attach)

Ed

[msbing916]

This is a work file so I am not sure if I should post it. Is there a way to post it and then delete after you look at it or could I send to you direct?

[EdMac]

If in doubt, don't send it - you don't know who I am!

Coming back to your original question, The formula that worked contained IF AND - I think you are confused as to what you want to achieve.

What you have done is add another set of IF statements thinking that this makes AND - this is not the way it works.

The structure is IF(and(this is true,that is true,other is true),Do this,else do that)

The 'else do that' can then be another IF statement but you are limited to 7 IF statements in a formula.

What you need to do is to first write it out on a piece of paper what you want to achieve i.e. what is the rule(s) that will determine the result, then make it into a formula and, if it does not work, use the formula auditing tools to see what is going wrong.

Regards

Ed

[daddylonglegs]

Often you can replace nested IFs with a simpler LOOKUP type structure, or in this case, INDEX/MATCH. E.g. in place of this part of your formula

=IF(Inputs!$B$8>=75 000,Inputs!$B$8*Inputs!$E79,IF(Inputs!$B$8>=50000, Inputs!$B$8*Inputs!$D79,IF(Inputs!$B$8>=15000,Inputs!$B$8*$C79,Inputs!$B$8*Inputs!$B79)))

you could use

=INDEX(Inputs!$C79:$E79,MATCH(Inputs!$B$8,{15000, 50000,75000}))*Inputs!$B$8