# Excel - IF Function Problem - Expert Solution

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
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
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
Excelchat Expert 23/04/2018 - 03:33
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.