if(and(...... formula including another if for false

Hi

I have a formula which im having a bit of trouble with returning the answer, it either needs to say £27.50, £2.75, or 0

Formula:

Please Login or Register  to view this content.

I have tried changing it around and keep getting #value error, what have i missed or what is missing, is this formula even possible?

Dan

Hi Thanks for your responses,

Pepe your formula returns 2.75 even though Rte is in cell I7

Xor yes Rte is the correct text and sometimes it will be in that cell othertimes not.

I have 3 conditions
If Rte and cell I29 = yes then its 27.50
If cell 29 = yes but there is no Rte in I27 then its 2.75
If neither conditions are met then its 0

I think because I29 must contain yes to add any value its not working correctly, and im unusre how to adjust the formula or tell the formula if it says yes but DOESNT contain Rte

Hi Yeah no problems please see attached thank you

Sample 28102013.xlsx

The formula Pepe posted works for me in cell I31 (and when filled right to K31)

In I31, result is 27.5 (I7 does contain Rte AND I29 = Yes)
In J31, result is 2.75 (J7 does NOT contain Rte AND J29 = Yes)
In K31, result is 0 (K7 does NOT contain Rte AND K29 does NOT equal Yes)

Another way to write it is:
=IF(I29="Yes",IF(ISNUMBER(FIND("Rte",I7)),27.50,2.75),0)

ooops my bad, i just re tried Pepes formula and realised i was posting it into column F! which was then looking at column I as i hadnt changed the formula, Sorry Pepe

Thank you both, both work and i appreciate both of your answers you have saved me much stress over this small formula