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.
