Alternate Calculation based on the content of a cell

[Krieger]

This is my first post on here, so my apologies if this has been answered before. I am struggling with how to phrase my question so googling for an answer has not been terribly successful.

Anyhow, I am building a spreadsheet to calculate annual updates to pensions, and the calculation changes when someone turns 60 or 65.

In short, I want a formula that will reference a cell containing the age, and if under that age does one calculation, and if over, another calculation.

If column A contains 'pension', B contains 'age' and C contains sex (M/F), I want column D to show the answer to A x E, or A x F, depending on whether the value in B is 60 or over (if female), or if B is 65 or over (if Male).
The age at which the calculation changes basis is 60 for female and 65 for male, so if the calculation could also recognise the content of column C and act accordingly, that would be marvellous. Otherwise, I would stick with a separate worksheet for each sex and a simpler formula.

Hopefully this makes sense. I am grateful for your help.

 

[etaf]

If column A contains 'pension', B contains 'age' and C contains sex (M/F), I want column D to show the answer to A x E, or A x F, depending on whether the value in B is 60 or over (if female), or if B is 65 or over (if Male).

an IF or Nested IF should do

=IF ( AND( A2="pension", B2 > = 60 , C2 = "female") , A2*E2 , IF ( AND( A2="pension", B2 > = 65 , C2 = "Male") , A2*F2 , "not pension age'))
BUT
A has Pension in so the
A2*E2 or A2 * F2 - is not going to work

If column A contains 'pension',

show the answer to A x E, or A x F,

can you explain in a little more detail
Here I have used H instead of A

Book3
	A	B	C	D	E	F	G	H
1
2	Pension	60	Female	12	1			12
3	Pension	63	Female	12	1			12
4	Pension	65	Female	12	1			12
5	Pension	60	male	not pension age	1			12
6	Pension	63	male	not pension age	1			12
7	Pension	65	male	36	1	3		12
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IF(AND(A2="pension",B2>=60,C2="female"),H2*E2,IF(AND(A2="pension",B2>=65,C2="Male"),H2*F2,"not pension age"))

 

[Krieger]

Thankyou for your reply - I am happy to clarify.
Column A will contain a pension amount in £.
Columns E and F will contain different factors, for example:
A = £200, E = 1.5, F = 1.8.
In column D then:
- If male and under 65 I want A x E (£300)
- If male and over 65 I want A x F (£360)
- If female and under 60 I want A x E (£300)
- If female and over 60 I want A x F (£360)

 

[etaf]

OK,
There will be a better way to do this - BUT staying with the Nested IF

=IF(AND(B2>=60,C2="female"),A2*F2,IF(AND(B2<60,C2="female"),A2*E2,IF(AND(B2>=65,C2="male"),A2*F2,IF(AND(B2<65,C2="male"),A2*E2,"Error"))))

I have added the error at the end, so if something not entered correctly of blank - BUT that can be changed to ""

Book3
	A	B	C	D	E	F	G	H	I
1
2	200	50	Female	300	1.5	1.8
3	200	63	Female	360	1.5	1.8
4	200	65	Female	360	1.5	1.8
5	200	60	male	300	1.5	1.8
6	200	65	male	360	1.5	1.8
7	200	70	male	360	1.5	1.8
8				Error
9
10		Column A will contain a pension amount in £. Columns E and F will contain different factors, for example: A = £200, E = 1.5, F = 1.8. In column D then: - If male and under 65 I want A x E (£300) - If male and over 65 I want A x F (£360) - If female and under 60 I want A x E (£300) - If female and over 60 I want A x F (£360)
11
12
13
14
15
16
17
18
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=IF(AND(B2>=60,C2="female"),A2*F2,IF(AND(B2<60,C2="female"),A2*E2,IF(AND(B2>=65,C2="male"),A2*F2,IF(AND(B2<65,C2="male"),A2*E2,"Error"))))

 

[jtakw]

Hi,

Try this also:

Book3.xlsx
	A	B	C	D	E	F
1	Pension	Age	Gender	Result	Under	Equal & Over
2	200	61	M	300	1.5	1.8
3	250	65	M	425	1.4	1.7
4	200	60	F	360	1.5	1.8
5	250	59	F	350	1.4	1.7
Sheet963

Cell Formulas
Range	Formula
D2:D5	D2	=A2*IF(OR(AND(B2<65,C2="M"),AND(B2<60,C2="F")),E2,F2)