**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a formula .... i need to decrease the premium an employee pays by 10% each year for ages 65-75. So when they are 65 it decreases 10%, age 66 it decreases 20% and so on. I tried
=IF(OR($D2+1<65,$D2+1=66,$D2+2=77, ...... ),J2,J2-(J2*0.1),J2(J2*0.2) .... ) D2 being the current age of the employee, with each column being year 1, year 2, etc.
However, excel will not allow me to do to IF D2+1=66, til age 75. It's too long. I know there has to be a better way! HELP!

Solved by F. F. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
23/04/2018 - 02:57

Hello!

Excelchat Expert
23/04/2018 - 02:58

Welcome to Got It Pro-Excel.

User
23/04/2018 - 02:58

hi!

Excelchat Expert
23/04/2018 - 02:58

Hello

Excelchat Expert
23/04/2018 - 02:58

Thanks for sharing your data

User
23/04/2018 - 02:58

i know there has to be an easy way to do what i want. but i don't know the formula for it.

Excelchat Expert
23/04/2018 - 02:58

Sure

Excelchat Expert
23/04/2018 - 02:59

Simply, let me know the location to place the result?

User
23/04/2018 - 02:59

i'm sorry?

Excelchat Expert
23/04/2018 - 03:00

Based on your data shown at the Document preview, can you tell me the cell to place the formula?

User
23/04/2018 - 03:00

wherever is easiest for you. when the employee reaches age 65, i need to decrease by 10% progressively each year the premium in each column until he reaches age 75.

User
23/04/2018 - 03:01

so when he's 65, it's 10% reduction. age 66, 20% reduction.

User
23/04/2018 - 03:01

i tried the IF function but it was too long and excel would not allow the formula.

Excelchat Expert
23/04/2018 - 03:02

For example, your B2 is 57, so the primium is exactly 6,830, right?

Excelchat Expert
23/04/2018 - 03:03

On year9, he will turn into 65, so instead of paying 17,350, there will be a reduction of 10%

Excelchat Expert
23/04/2018 - 03:04

correct?

User
23/04/2018 - 03:04

yes

Excelchat Expert
23/04/2018 - 03:04

Thanks

Excelchat Expert
23/04/2018 - 03:05

Please hold on, I'm going to create the formula at row3 for you

Excelchat Expert
23/04/2018 - 03:09

I've created your C3 now, checking for the age at B2, if less than 65, or 65 or 66

Excelchat Expert
23/04/2018 - 03:10

So, the formula looks like this: =If(B2<65,C2,If(B2=65,C2-(0.1*C2),C2-(0.2*C2)))

User
23/04/2018 - 03:10

yes. i did that. but when i write the formula for the ten years, excel tells me i have too many ifs

Excelchat Expert
23/04/2018 - 03:11

Going through the next years makes another criteria of IF statement

Excelchat Expert
23/04/2018 - 03:11

So, we can continue at D3 if C3 already works for you?

User
23/04/2018 - 03:11

so each column should have a different formula?

Excelchat Expert
23/04/2018 - 03:12

yes

Excelchat Expert
23/04/2018 - 03:12

Because we need to add their age as they go through

User
23/04/2018 - 03:12

so you changed the age in column b?

Excelchat Expert
23/04/2018 - 03:12

As they go through the years, their age must also be increase

Excelchat Expert
23/04/2018 - 03:12

nope?

User
23/04/2018 - 03:12

i thought there would be one big formula!

Excelchat Expert
23/04/2018 - 03:13

Let me try again for the next formula

User
23/04/2018 - 03:13

so i'm not that stupid. lol.

Excelchat Expert
23/04/2018 - 03:13

Few minutes more

User
23/04/2018 - 03:13

can we figure it out for someone is not yet 65?

Excelchat Expert
23/04/2018 - 03:13

sure

Excelchat Expert
23/04/2018 - 03:14

Back to 57, and he will be turning 65 on year year9, right?

User
23/04/2018 - 03:14

yes. correct.

User
23/04/2018 - 03:14

that was part of the formula i wrote.

Excelchat Expert
23/04/2018 - 03:15

Okay let me handle the formula for you

Excelchat Expert
23/04/2018 - 03:15

yes, you're right

User
23/04/2018 - 03:15

it added the number for the age.

Excelchat Expert
23/04/2018 - 03:15

yes, we need to add their age into 1 every year

User
23/04/2018 - 03:15

if B2<65=C2

User
23/04/2018 - 03:16

starting with the 2nd year

Excelchat Expert
23/04/2018 - 03:16

yes that's right

User
23/04/2018 - 03:16

=IF(OR($D2<65,$D2>75),H2,H2-(H2*0.1))

User
23/04/2018 - 03:16

this is the formula in my spreadsheet.

User
23/04/2018 - 03:16

for the first column. then

Excelchat Expert
23/04/2018 - 03:17

When it reaches 75, what will happen?

User
23/04/2018 - 03:17

=IF(OR($D2+2<65,$D2+2>75),L2,L2-(L2*0.1))

User
23/04/2018 - 03:17

my boss tells me it should reach 0.

User
23/04/2018 - 03:17

in year 9 the premium decreses by 10%

User
23/04/2018 - 03:17

in year 10 the premium decreases by 20%

User
23/04/2018 - 03:17

and so on

Excelchat Expert
23/04/2018 - 03:17

the percentage will increase into 10%, then 20%, 30% and so on?

User
23/04/2018 - 03:17

yes.

Excelchat Expert
23/04/2018 - 03:18

Okay, let me handle the formula, please bear with me few minutes

User
23/04/2018 - 03:18

thank you!

Excelchat Expert
23/04/2018 - 03:18

My pleasure

User
23/04/2018 - 03:21

and here is the other kink ... i have 52 employees at different ages.

User
23/04/2018 - 03:21

so the reduction obviously starts in different columns

Excelchat Expert
23/04/2018 - 03:21

The formula will soon work for your 52 employees

Excelchat Expert
23/04/2018 - 03:21

in different ages too

Excelchat Expert
23/04/2018 - 03:27

Alright, please take a look at the formula now

User
23/04/2018 - 03:27

oh my. what am i looking at?

Excelchat Expert
23/04/2018 - 03:27

Try to change the age at B2, to check the place of reduction

User
23/04/2018 - 03:28

that's amazing.

Excelchat Expert
23/04/2018 - 03:28

So, the formula looks like this

Excelchat Expert
23/04/2018 - 03:28

=If($B$2+(mid(D1,5,len(D1)-4)-1)<65,D2,If($B$2+(mid(D1,5,len(D1)-4)-1)=65,D2-(0.1*D2),D2-(0.2*D2)))

User
23/04/2018 - 03:28

can you explain the formula? i never would've been able to come up with that.

Excelchat Expert
23/04/2018 - 03:28

At first, we are checking their age

Excelchat Expert
23/04/2018 - 03:29

We are increasing their age per year, so we need to check if their age is turning onto 65 or 66

Excelchat Expert
23/04/2018 - 03:29

For the age checking, the formula looks like this, =$B$2+(mid(D1,5,len(D1)-4)-1)

Excelchat Expert
23/04/2018 - 03:29

This is shown at row 5

User
23/04/2018 - 03:29

what is mid and len? what are those instructions?

Excelchat Expert
23/04/2018 - 03:30

While dragging the formula to the right, it automatically computes the age

Excelchat Expert
23/04/2018 - 03:30

That means, we are getting the number at row1,

User
23/04/2018 - 03:30

i need to add a column showing the decrease after each year.

Excelchat Expert
23/04/2018 - 03:30

Just like Year2, it gets number 2 only

User
23/04/2018 - 03:32

so if i copy these formulas, what will i need to adjust?

Excelchat Expert
23/04/2018 - 03:32

Well, does the formula of getting the percentage reduction now works for you?

Excelchat Expert
23/04/2018 - 03:32

It will automatically adjust

Excelchat Expert
23/04/2018 - 03:32

You only need the age at B2 to change

User
23/04/2018 - 03:32

it seems as if it does, but the final spreadsheet will be different. the formulas need to be put in the column beside the premium column.

User
23/04/2018 - 03:33

can i save this worksheet that you've done somehow?

Excelchat Expert
23/04/2018 - 03:33

sure

Excelchat Expert
23/04/2018 - 03:33

You may download it

Excelchat Expert
23/04/2018 - 03:33

So, this spreadsheet is shown by row, but your final spreadsheet is by column, correct?

User
23/04/2018 - 03:34

yes. i will be adding a new column beside each year showing the reduced premium.

Excelchat Expert
23/04/2018 - 03:34

The layout we have today seems different of yours?

User
23/04/2018 - 03:34

slightly.

Excelchat Expert
23/04/2018 - 03:34

Okay, you will only need to identify the location of B2

Excelchat Expert
23/04/2018 - 03:34

as age

User
23/04/2018 - 03:35

i will be adding a column beside C (new column d) labeled reduced premium.

User
23/04/2018 - 03:35

okay. my spreadsheet has it in column d.

Excelchat Expert
23/04/2018 - 03:35

Yeah, got i`t

Excelchat Expert
23/04/2018 - 03:35

It will be transpose order

User
23/04/2018 - 03:35

my fingers are crossed. i dont know the formula you created, i can pretty logically figure out if and or, but this is greek to me!

Excelchat Expert
23/04/2018 - 03:35

instead of rows going to the right, it will be from top to bottom

Excelchat Expert
23/04/2018 - 03:36

Well, the logic of the formula goes the same way

Excelchat Expert
23/04/2018 - 03:36

First, copy the first formula

User
23/04/2018 - 03:36

i meant the way you wrote the formula. the commands are not familiar to me.

Excelchat Expert
23/04/2018 - 03:36

Then the second formula next to it

User
23/04/2018 - 03:37

i have to change the $B$2 to $D$2, correct.

User
23/04/2018 - 03:37

wait, there's two different formulas. which one do i use where?

User
23/04/2018 - 03:37

column d is the formula I should use?

Excelchat Expert
23/04/2018 - 03:37

A dollar sign of $B$2 means B2 will never change the location once the formula are drag down

Excelchat Expert
23/04/2018 - 03:38

or copied to the next location

User
23/04/2018 - 03:38

right. so my age is in column d, not b, so i change that.

User
23/04/2018 - 03:38

and make it absolute with $

Excelchat Expert
23/04/2018 - 03:38

Yes, change it into $D$2

User
23/04/2018 - 03:38

the D1 represents the year in the long formula?

User
23/04/2018 - 03:38

but what do i do with the shorter formula?

Excelchat Expert
23/04/2018 - 03:38

$ will make the location static

User
23/04/2018 - 03:39

do i only use the longer formula?

Excelchat Expert
23/04/2018 - 03:39

Well, let's try to change bposition for you

Excelchat Expert
23/04/2018 - 03:40

Please take a look at sheet2, is it the same with yours

Excelchat Expert
23/04/2018 - 03:41

Please check sheet2 for now

User
23/04/2018 - 03:41

no, it's not the same.

Excelchat Expert
23/04/2018 - 03:42

May I know the difference?

Excelchat Expert
23/04/2018 - 03:42

If you have another column into it, just add the same

Excelchat Expert
23/04/2018 - 03:42

The most important is you've made the right logic on it

User
23/04/2018 - 03:43

right. i need to print off the formula, and make my adjustments

Excelchat Expert
23/04/2018 - 03:43

At the formula we have, first we determine their ages

User
23/04/2018 - 03:43

looking at my sheet and your sheet

Excelchat Expert
23/04/2018 - 03:43

sure

Excelchat Expert
23/04/2018 - 03:43

You may download it too

User
23/04/2018 - 03:43

but will the formula increase the amount of the reduction? it looks to me like its only .1 and .2?

Excelchat Expert
23/04/2018 - 03:44

And this final formula would help: =If($B$2+(mid(A5,5,len(A5)-4)-1)<65,B5,If($B$2+(mid(A5,5,len(A5)-4)-1)=65,B5-(0.1*B5),B5-(0.2*B5)))

Excelchat Expert
23/04/2018 - 03:44

yes

User
23/04/2018 - 03:44

third year it's .3, fourth year it's .5

Excelchat Expert
23/04/2018 - 03:44

So we need to add more percent on it

User
23/04/2018 - 03:44

i don't really understand the formula

User
23/04/2018 - 03:44

will excel accept it?

Excelchat Expert
23/04/2018 - 03:44

We can continue adding the IF formula

User
23/04/2018 - 03:44

i had added all the percentages to my formula and excel rejected it

Excelchat Expert
23/04/2018 - 03:45

Please let me know which part you did not understand so that I can explain?

User
23/04/2018 - 03:45

i don't understand the commands. mid len

Excelchat Expert
23/04/2018 - 03:45

If you missed some part of the syntax, excel will not react on it

User
23/04/2018 - 03:46

no. it understood. but it said i had too many.

User
23/04/2018 - 03:46

i forget the exact wording.

Excelchat Expert
23/04/2018 - 03:46

Mid-Len means we are cutting the part at the mid of a string or content

Excelchat Expert
23/04/2018 - 03:46

In this example formula: mid(A5,5,len(A5)-4)-1)

Excelchat Expert
23/04/2018 - 03:48

Means we are cutting the part of the word at A5 that contains "Year3", starting from no. 5 location, such as 3, then we get 3 on it

Excelchat Expert
23/04/2018 - 03:48

So, if the word is Year4 or year5, we get either 4 or 5 too

Excelchat Expert
23/04/2018 - 03:49

Got it how MID functions do?

User
23/04/2018 - 03:49

i think so. not so much to be honest.

User
23/04/2018 - 03:49

but as long as the formula will work.

Excelchat Expert
23/04/2018 - 03:49

lol

User
23/04/2018 - 03:49

i've put one employee's premiums in .

Excelchat Expert
23/04/2018 - 03:50

Anyway I understand

User
23/04/2018 - 03:50

this is what my spreadsheet looks like.

Excelchat Expert
23/04/2018 - 03:50

I just hope, we have much time to explain with yu

Excelchat Expert
23/04/2018 - 03:50

Which?

User
23/04/2018 - 03:50

it's okay if i don't understand the formula. as long as the numbers are okay and i don't have to change the formula for each column, each employee

User
23/04/2018 - 03:51

line 24 and 25

User
23/04/2018 - 03:51

it looks better than that, but you get the gist

Excelchat Expert
23/04/2018 - 03:51

Oh thanks for confirming it

Excelchat Expert
23/04/2018 - 03:51

What does the red means

User
23/04/2018 - 03:52

that's the column that i need to insert the reduced premium into, if applicable

Excelchat Expert
23/04/2018 - 03:52

okay, I'll try to copy the formula in

User
23/04/2018 - 03:55

but he's still 64 in year 1. his reduction starts in year 2.

Excelchat Expert
23/04/2018 - 03:55

yes

User
23/04/2018 - 03:55

okay. i'm jumping the gun. sorry.

Excelchat Expert
23/04/2018 - 03:55

D25 will be like this formula that works

Excelchat Expert
23/04/2018 - 03:56

=If($B$25+(mid(C24,5,len(C24)-4)-1)<65,C25,If($B$25+(mid(B25,5,len(C24)-4)-1)=65,C25-(0.1*C25),C25-(0.2*C25)))

User
23/04/2018 - 03:56

so i will need to change this formula for the reduction for all 10 years?

Excelchat Expert
23/04/2018 - 03:57

You will only copy D25 to location red at each year

Excelchat Expert
23/04/2018 - 03:57

seems automatically change now

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*