IF, AND, OR Combined Statement

[yvettew78]

Hi Everyone

I am trying to work out a formula like the following but it's nor working. What am I doing wrong? If someone can help, that would be great.

Basically CELL E4 (as highlighted below) need to be and OR statement, but I am not sure how to show it.

Thank you.

=IF(AND(E3="Cash",E4="Residential-House",E4="Residential-Shed",E5="Single"),VLOOKUP(C10,'RRP Pricing'!A2:G58,2,FALSE),IF(AND(E3="Cash",E4="Residential-House",E4="Residential-Shed",E5="Three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,3,FALSE),IF(AND(E3="Cash",E4="Commercial",E5="Three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,4,FALSE),IF(AND(E3="Finance",E4="Residential-House",E4="Residential-Shed",E5="Single"),VLOOKUP(C10,'RRP Pricing'!A2:G58,5,FALSE),IF(AND(E3="Finance",E4="Residential-House",E4="Residential-Shed",E5="Three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,6,FALSE),IF(AND(E3="Finance",E4="Commercial",E5="Three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,7,FALSE),0))))))

 

[mole999]

rather than working an OR in there, how about changing your bold selection for

,LEFT(E4,10)="Residential" therefore you ignore House and shed and shorthen the formula a little

 

[yvettew78]

Hey There. thanks for replying to me.

is this correct??
=IF(AND(E3="Cash",LEFT(E4,10)="Residential",E5="Single"),VLOOKUP(C10,'RRP Pricing'!A2:G58,2,FALSE),IF(AND(E3="Cash",LEFT(E4,10)="Residential",E5="Three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,3,FALSE),IF(AND(E3="Cash",E4="Commercial",E5="Three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,4,FALSE),IF(AND(E3="Finance",LEFT(E4,10)="Residential",E5="Single"),VLOOKUP(C10,'RRP Pricing'!A2:G58,5,FALSE),IF(AND(E3="Finance",,LEFT(E4,10)="Residential",E5="Three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,6,FALSE),IF(AND(E3="Finance",E4="Commercial",E5="Three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,7,FALSE),0))))))

it doesnt seem to work?

 

[mole999]

it might be residential is 11 letters long, in which case change 10 to 11, not always easy to count on screen

you have also created a double comma Finance",,LEFT(E

 

[yvettew78]

Thanks heaps - sorry I wasn't sure what the "10" represented. I was just about to ask you......that worked.

Really appreciate your help!

Perhaps you could help me with an issue I have just come across...........I have created a RESET button and script to reset all fields of the calculator I am creating. But the user can click the PLUS sign to add a new row when they need to add a new item. This works but when I RESET the calculator the new rows that were added are still there and some of the cell references in the RESET script are incorrect. How can I fix this? Should I add a function so that it removed the rows the user has added? If so, what function would be needed?

Thanks again for your help.

 

[mole999]

@yvettew78 that is totally a new question, and should be a new topic. I can't imagine what any of that looks like

 

[yvettew78]

@mole999 sorry you are right. I have posted a new thread. Sorry sometimes its hard to explain! LOL