Struggling for a formula

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi

I've tried this and tried this and just can't work out how to do it.
And advice would be hugely appreciated.

Is there an easy way of having the following decided in a single cell:

K16 contains age, rangename cltage
K17 contains partner's age, if there is one, rangename ptnrage

I need a value in a third cell, P17 which conforms to the following
rules:

both aged under 18 = £70.70
at least one aged 18+ but both under 60 = £92.82
at least one aged 60+ but both under 65 = £181.70
at least one aged 65+ = £207.00

Thanks in advance

Steve
 
Maybe:-

=IF(AND(cltage<18,ptnrage<18),70.7,IF(AND(OR(cltage>18,ptnrage>18),cltage<60,ptnrage<60),92.82,IF(AND(OR(cltage>60,ptnrage>60),cltage<65,ptnrage<65),181.7,207)))


Format cell as currency.

Mike
 
Hi,

Forgot to mention that while this satisifes the table you posted it will
fail when (say) both are 18 so you need to decide when and where to change
the > to >=

you might consider this modification to your logic:-

=IF(AND(cltage<=18,ptnrage<=18),70.7,IF(AND(OR(cltage>18,ptnrage>18),cltage<=60,ptnrage<=60),92.82,IF(AND(OR(cltage>60,ptnrage>60),cltage<=65,ptnrage<=65),181.7,207)))

Mike
 
Mike said:
you might consider this modification to your logic:-

=IF(AND(cltage<=18,ptnrage<=18),70.7,IF(AND(OR(cltage>18,ptnrage>18),cltage-<=60,ptnrage<=60),92.82,IF(AND(OR(cltage>60,ptnrage>60),cltage<=65,ptnrage<-=65),181.7,207)))
Click to expand...

That's great; thanks Mike.
 
Back
Top