Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Struggling for a formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Struggling for a formula
Maybe:-
=IF(AND(cltage<18,ptnrage<18),70.7,IF(AND(OR(cltag e18,ptnrage18),cltage<60,ptnrage<60),92.82,IF(AN D(OR(cltage60,ptnrage60),cltage<65,ptnrage<65),1 81.7,207))) Format cell as currency. Mike "Steve" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Struggling for a formula
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(clt age18,ptnrage18),cltage<=60,ptnrage<=60),92.82,I F(AND(OR(cltage60,ptnrage60),cltage<=65,ptnrage< =65),181.7,207))) Mike "Mike H" wrote: Maybe:- =IF(AND(cltage<18,ptnrage<18),70.7,IF(AND(OR(cltag e18,ptnrage18),cltage<60,ptnrage<60),92.82,IF(AN D(OR(cltage60,ptnrage60),cltage<65,ptnrage<65),1 81.7,207))) Format cell as currency. Mike "Steve" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Struggling for a formula
Mike H wrote:
you might consider this modification to your logic:- =IF(AND(cltage<=18,ptnrage<=18),70.7,IF(AND(OR(clt age18,ptnrage18),cltage-<=60,ptnrage<=60),92.82,IF(AND(OR(cltage60,ptnrag e60),cltage<=65,ptnrage<-=65),181.7,207))) That's great; thanks Mike. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Struggling with IF formula...is it even the right one for me? | New Users to Excel | |||
Struggling to plot where I want | Charts and Charting in Excel | |||
Struggling IF value | Excel Discussion (Misc queries) | |||
Struggling for a solution | Excel Worksheet Functions | |||
Help, I'm Struggling! | New Users to Excel |