Excel Formula Cannot Figure Out

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
Topic Author
Mr400meterdash
Posts: 69
Joined: Wed Dec 13, 2017 10:24 am

Excel Formula Cannot Figure Out

Post by Mr400meterdash » Thu Dec 19, 2019 12:01 pm

Hello Everyone,

I am trying to make an excel spreadsheet that can track PTO.

I am trying to get it to base everything off the current date.

Here is what we have
Up to and including year 2 is 128 hours
Beginning year 3 is 168
Beginning year 8 is 2920
Beginning year 12 is 4380

If you do not know how to write the equation, do you know a site to visit that I could ask that question in as well?
I am getting stumped on this so any help would be appreciated!

Thanks!

magicrat
Posts: 1010
Joined: Sat Nov 29, 2014 7:04 pm

Re: Excel Formula Cannot Figure Out

Post by magicrat » Thu Dec 19, 2019 12:04 pm

The formula for today's date is =today()

However, I cannot tell what equation you are trying to write based on your post.

Topic Author
Mr400meterdash
Posts: 69
Joined: Wed Dec 13, 2017 10:24 am

Re: Excel Formula Cannot Figure Out

Post by Mr400meterdash » Thu Dec 19, 2019 12:09 pm

I am trying to write an equation that says if todays date- their hire date is less than 1095 (which is 3 years of days) than the answer would be 128, if it is over 1095 but less than 2920(8 years) than the answer is 168, if it is between 2920 but less than 4380(12 years) than it is 208, if it is over 4380, than the answer would be 248. I just dont know how to string an If statement that long or if an if statement is the way to go.

if a1 is the hire date

The formula =If(Today()-A1>1095, 168, 128) works just fine for the first group but I cant add on to the formula any further without help from someone that knows what they are doing.

TNWoods
Posts: 89
Joined: Sun Feb 10, 2019 10:04 am

Re: Excel Formula Cannot Figure Out

Post by TNWoods » Thu Dec 19, 2019 12:19 pm

You could nest several "IF" formulas by replacing the "128" with a complete "IF" formula, and then nest one in that formula, and so on.

You could also calculate years of employment (use "rounddown" to get a whole number) and use that value for a "vlookup" to a small table of the correct answers.

TNWoods
Last edited by TNWoods on Thu Dec 19, 2019 12:21 pm, edited 1 time in total.

MeasureTwiceCutOnce
Posts: 5
Joined: Tue Sep 10, 2019 9:30 am

Re: Excel Formula Cannot Figure Out

Post by MeasureTwiceCutOnce » Thu Dec 19, 2019 12:20 pm

You can set it up as a series of if then statements. I used 3 weeks, 4 weeks, 5 weeks, and 6 weeks as my PTO numbers but you can put in whatever you want.

=IF(TODAY()-A1>4380,240,IF(TODAY()-A1>2920,200,IF(TODAY()-A1>1095,160,120)))

redbird24
Posts: 12
Joined: Fri Mar 22, 2019 2:55 pm

Re: Excel Formula Cannot Figure Out

Post by redbird24 » Thu Dec 19, 2019 12:21 pm

Mr400meterdash wrote:
Thu Dec 19, 2019 12:09 pm
I am trying to write an equation that says if todays date- their hire date is less than 1095 (which is 3 years of days) than the answer would be 128, if it is over 1095 but less than 2920(8 years) than the answer is 168, if it is between 2920 but less than 4380(12 years) than it is 208, if it is over 4380, than the answer would be 248. I just dont know how to string an If statement that long or if an if statement is the way to go.

if a1 is the hire date

The formula =If(Today()-A1>1095, 168, 128) works just fine for the first group but I cant add on to the formula any further without help from someone that knows what they are doing.
I would string multiple if functions together. For the false result part, you add in another if function. Either start with greater than the top number or lower than the bottom number.
=IF(Today()-A1<1095, 128, IF(Today()-A1<2920, 168, IF(Today()-A1<4380, 208, 248)))

You could also go the other way and start with the lager number 4380 and go A1>4380

mlipps
Posts: 1006
Joined: Thu Jun 21, 2012 9:35 am

Re: Excel Formula Cannot Figure Out

Post by mlipps » Thu Dec 19, 2019 12:21 pm

You don't need to nest the IF statement, you can instead use the function "IFS" to evaluate multiple if statements:

https://exceljet.net/excel-functions/excel-ifs-function

User avatar
fortfun
Posts: 2679
Joined: Tue Apr 19, 2016 7:31 pm

Re: Excel Formula Cannot Figure Out

Post by fortfun » Thu Dec 19, 2019 12:22 pm

Mr400meterdash wrote:
Thu Dec 19, 2019 12:09 pm
I am trying to write an equation that says if todays date- their hire date is less than 1095 (which is 3 years of days) than the answer would be 128, if it is over 1095 but less than 2920(8 years) than the answer is 168, if it is between 2920 but less than 4380(12 years) than it is 208, if it is over 4380, than the answer would be 248. I just dont know how to string an If statement that long or if an if statement is the way to go.

if a1 is the hire date

The formula =If(Today()-A1>1095, 168, 128) works just fine for the first group but I cant add on to the formula any further without help from someone that knows what they are doing.
Something like this:
=if(x>10,1095,if(x>9,168,128))

Use your numbers though.

Topic Author
Mr400meterdash
Posts: 69
Joined: Wed Dec 13, 2017 10:24 am

Re: Excel Formula Cannot Figure Out

Post by Mr400meterdash » Thu Dec 19, 2019 12:24 pm

That worked!

Thank you!

TNWoods
Posts: 89
Joined: Sun Feb 10, 2019 10:04 am

Re: Excel Formula Cannot Figure Out

Post by TNWoods » Thu Dec 19, 2019 12:26 pm

I was all psyched to learn about the "IFS" and then I see it's only available for Office 2019. I'm a couple versions back...

I'll remember it for the future when we get upgraded. If I'm still working.

TNWoods

lotusflower
Posts: 288
Joined: Thu Oct 24, 2013 12:32 am

Re: Excel Formula Cannot Figure Out

Post by lotusflower » Thu Dec 19, 2019 12:35 pm

Wish I worked at your company, 4380 hours is a lot of PTO!
You can nest logical IFs, but personally I think complicated Excel formulas are a big problem with Excel, and using a complicated formula for anything related to HR and comp puts a business at risk. Especially when you start copying formulas and it's easy to get relative vs absolute cell references wrong.

I would make a column that computes the number of years worked, and then I would make a table, maybe on another sheet, that has years of service in one column and PTO hours in the next column. Then you can use VLOOKUP in another column to pull the right pto value out of the table.

Gryphon
Posts: 150
Joined: Sat May 07, 2016 11:43 am
Location: Missouri

Re: Excel Formula Cannot Figure Out

Post by Gryphon » Thu Dec 19, 2019 1:17 pm

lotusflower wrote:
Thu Dec 19, 2019 12:35 pm
Wish I worked at your company, 4380 hours is a lot of PTO!
You can nest logical IFs, but personally I think complicated Excel formulas are a big problem with Excel, and using a complicated formula for anything related to HR and comp puts a business at risk. Especially when you start copying formulas and it's easy to get relative vs absolute cell references wrong.

I would make a column that computes the number of years worked, and then I would make a table, maybe on another sheet, that has years of service in one column and PTO hours in the next column. Then you can use VLOOKUP in another column to pull the right pto value out of the table.
Another advantage is that it puts the PTO thresholds in one place. If your company's PTO policy changes, you update one spreadsheet with the new values & you're done - every other spreadsheet that references it starts using the new values. If you have those PTO thresholds embedded in individual formulae spread all over the place, you have a much bigger job ahead of you.

acegolfer
Posts: 2005
Joined: Tue Aug 25, 2009 9:40 am

Re: Excel Formula Cannot Figure Out

Post by acegolfer » Thu Dec 19, 2019 1:42 pm

TNWoods wrote:
Thu Dec 19, 2019 12:26 pm
I was all psyched to learn about the "IFS" and then I see it's only available for Office 2019. I'm a couple versions back...

I'll remember it for the future when we get upgraded. If I'm still working.

TNWoods
FYI, almost all Excel 2019 functions including IFS are supported in Google Sheets.

User avatar
neurosphere
Posts: 3584
Joined: Sun Jan 17, 2010 1:55 pm

Re: Excel Formula Cannot Figure Out

Post by neurosphere » Thu Dec 19, 2019 2:12 pm

acegolfer wrote:
Thu Dec 19, 2019 1:42 pm
TNWoods wrote:
Thu Dec 19, 2019 12:26 pm
I was all psyched to learn about the "IFS" and then I see it's only available for Office 2019. I'm a couple versions back...

I'll remember it for the future when we get upgraded. If I'm still working.

TNWoods
FYI, almost all Excel 2019 functions including IFS are supported in Google Sheets.
First, I'm very excited to have learned today about "IFS". Had I known about it just yesterday, it would have save me an hour working on a withholding calculator! E.g. "IFS adjusted wage income is between X and Y then Z, if between A and B then C" etc. Second, it's great that it works in Google Sheets as well. Thanks for the info.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

GrowthSeeker
Posts: 860
Joined: Tue May 15, 2018 10:14 pm

Re: Excel Formula Cannot Figure Out

Post by GrowthSeeker » Thu Dec 19, 2019 2:15 pm

Mr400meterdash wrote:
Thu Dec 19, 2019 12:01 pm
Hello Everyone,

I am trying to make an excel spreadsheet that can track PTO.

I am trying to get it to base everything off the current date.

Here is what we have
Up to and including year 2 is 128 hours
Beginning year 3 is 168
Beginning year 8 is 2920
Beginning year 12 is 4380

If you do not know how to write the equation, do you know a site to visit that I could ask that question in as well?
I am getting stumped on this so any help would be appreciated!

Thanks!
I can't help but think you have an error in these numbers.
2920/8 = 365
4380/12 = 365

For the first two, I think you're saying the number of PTO hours per year a person gets if they have been working <=2 years, or between 2 and 3 years.
For the last two, I think you're just saying the number of days which are in so many years.

Why not just tell us what the rule is for how much PTO someone gets when they have working how many years? Maybe we need to know whether PTO hours are "use it or lose it" on the anniversary date of employment vs they continue to accrue. Does an employee who has been there only 6 months have the exact same amount of PTO as someone who has been there 2 years? If not, what's the formula? I can't believe it's a simple as you put in the 4 lines of information.

Thanks.
If we know more details I think we can make a spreadsheet formula.
Just because you're paranoid doesn't mean they're NOT out to get you.

Fresh Air
Posts: 43
Joined: Thu May 19, 2016 9:04 am

Re: Excel Formula Cannot Figure Out

Post by Fresh Air » Thu Dec 19, 2019 2:35 pm

mlipps wrote:
Thu Dec 19, 2019 12:21 pm
You don't need to nest the IF statement, you can instead use the function "IFS" to evaluate multiple if statements:

https://exceljet.net/excel-functions/excel-ifs-function
As someone who has painfully nested far too many functions, you just made my day. Thank you, thank you.

User avatar
Oicuryy
Posts: 1429
Joined: Thu Feb 22, 2007 10:29 pm

Re: Excel Formula Cannot Figure Out

Post by Oicuryy » Thu Dec 19, 2019 2:51 pm

=CHOOSE(MIN(YEARFRAC(A1,TODAY(),1),12)+1,128,128,128,168,168,168,168,168,208,208,208,208,248)
Money is fungible | Abbreviations and Acronyms

acegolfer
Posts: 2005
Joined: Tue Aug 25, 2009 9:40 am

Re: Excel Formula Cannot Figure Out

Post by acegolfer » Thu Dec 19, 2019 2:57 pm

Fresh Air wrote:
Thu Dec 19, 2019 2:35 pm
mlipps wrote:
Thu Dec 19, 2019 12:21 pm
You don't need to nest the IF statement, you can instead use the function "IFS" to evaluate multiple if statements:

https://exceljet.net/excel-functions/excel-ifs-function
As someone who has painfully nested far too many functions, you just made my day. Thank you, thank you.
If you like IFS, also consider SWITCH( ) and CHOOSE( )

Topic Author
Mr400meterdash
Posts: 69
Joined: Wed Dec 13, 2017 10:24 am

Re: Excel Formula Cannot Figure Out

Post by Mr400meterdash » Thu Dec 19, 2019 3:17 pm

GrowthSeeker wrote:
Thu Dec 19, 2019 2:15 pm
Mr400meterdash wrote:
Thu Dec 19, 2019 12:01 pm
Hello Everyone,

I am trying to make an excel spreadsheet that can track PTO.

I am trying to get it to base everything off the current date.

Here is what we have
Up to and including year 2 is 128 hours
Beginning year 3 is 168
Beginning year 8 is 2920
Beginning year 12 is 4380

If you do not know how to write the equation, do you know a site to visit that I could ask that question in as well?
I am getting stumped on this so any help would be appreciated!

Thanks!
I can't help but think you have an error in these numbers.
2920/8 = 365
4380/12 = 365

For the first two, I think you're saying the number of PTO hours per year a person gets if they have been working <=2 years, or between 2 and 3 years.
For the last two, I think you're just saying the number of days which are in so many years.

Why not just tell us what the rule is for how much PTO someone gets when they have working how many years? Maybe we need to know whether PTO hours are "use it or lose it" on the anniversary date of employment vs they continue to accrue. Does an employee who has been there only 6 months have the exact same amount of PTO as someone who has been there 2 years? If not, what's the formula? I can't believe it's a simple as you put in the 4 lines of information.

Thanks.
If we know more details I think we can make a spreadsheet formula.
You are correct on me having my numbers incorrect. Sorry!

year 1-end of 2 is 128
beginning of year 3 to end of year 7 is 168
beginning of year 8 to end of year 11 is 208
beginning of year 12 to forever is 248

If a person joins in October they get 3 months worth of PTO so 32 hours. Rollover is 5 days. PTO days accrue annually so we get all days Jan 1 of the next year.

The problem I am running into at the moment is having the formula understand that it cant change the numbers until the next year.

For example. Lets say the hire date was 12-20-17. This year I should still have 208 hours of PTO but as soon as tomorrow hits, it will give me 40 more hours.

For everyone's knowledge, this is just a prototype of something I may pitch to HR. It is not a big company or anything like that but thought I could reduce costs for us by making something simpler. I also love playing around with excel so thought why not. I would create a database for this if it was going to be a true idea but I like making things in excel first.

Thanks!

GrowthSeeker
Posts: 860
Joined: Tue May 15, 2018 10:14 pm

Re: Excel Formula Cannot Figure Out

Post by GrowthSeeker » Thu Dec 19, 2019 3:34 pm

Mr400meterdash wrote:
Thu Dec 19, 2019 3:17 pm
Rollover is 5 days. PTO days accrue annually so we get all days Jan 1 of the next year.
I'm not sure I understand what "rollover" means in this context. Does this mean that if as of Dec 31 of a given year you still have, say, 11 days of unused PTO then on Jan 1 you have your full allotment for the new year, plus 5 of the 11 unused days from the previous year? But if you had used up all your days as of Dec 31, then on Jan 1 you'd get just the allotment for the new year?
For everyone's knowledge, this is just a prototype of something I may pitch to HR.
All the more reason to get this exactly right, so it works perfectly. Don't want it partly working and partly wrong; don't want to be like 'poisoning the brand'.
Just because you're paranoid doesn't mean they're NOT out to get you.

lotusflower
Posts: 288
Joined: Thu Oct 24, 2013 12:32 am

Re: Excel Formula Cannot Figure Out

Post by lotusflower » Thu Dec 19, 2019 3:37 pm

Mr400meterdash wrote:
Thu Dec 19, 2019 3:17 pm
I am trying to make an excel spreadsheet that can track PTO.

You are correct on me having my numbers incorrect. Sorry!

year 1-end of 2 is 128
beginning of year 3 to end of year 7 is 168
beginning of year 8 to end of year 11 is 208
beginning of year 12 to forever is 248

If a person joins in October they get 3 months worth of PTO so 32 hours. Rollover is 5 days. PTO days accrue annually so we get all days Jan 1 of the next year.

The problem I am running into at the moment is having the formula understand that it cant change the numbers until the next year.

For example. Lets say the hire date was 12-20-17. This year I should still have 208 hours of PTO but as soon as tomorrow hits, it will give me 40 more hours.

For everyone's knowledge, this is just a prototype of something I may pitch to HR. It is not a big company or anything like that but thought I could reduce costs for us by making something simpler. I also love playing around with excel so thought why not. I would create a database for this if it was going to be a true idea but I like making things in excel first.

Thanks!
I hope you can go meta on this and realize why complicated Excel formulas are not that great for HR/Comp issues. The first issue is mistakes which you already made, the second is complexity. Your rules seem simple but as you start describing them it's not quite that simple after all since there are grant dates on Jan 1 and on certain threshold anniversaries, plus rollover limitations.

You can still do it in Excel (maybe), but I would stage your formula into a few columns and lookups. You can always hide columns with intermediate values but if you need to check or verify the formulas it's much easier to have each stage in its own column.

Are you trying to make a tool that helps HR/Finance? A tool that helps your co-workers? Are you just trying to illustrate a new compensation policy you are proposing?

Topic Author
Mr400meterdash
Posts: 69
Joined: Wed Dec 13, 2017 10:24 am

Re: Excel Formula Cannot Figure Out

Post by Mr400meterdash » Thu Dec 19, 2019 3:47 pm

lotusflower wrote:
Thu Dec 19, 2019 3:37 pm
Mr400meterdash wrote:
Thu Dec 19, 2019 3:17 pm
I am trying to make an excel spreadsheet that can track PTO.

You are correct on me having my numbers incorrect. Sorry!

year 1-end of 2 is 128
beginning of year 3 to end of year 7 is 168
beginning of year 8 to end of year 11 is 208
beginning of year 12 to forever is 248

If a person joins in October they get 3 months worth of PTO so 32 hours. Rollover is 5 days. PTO days accrue annually so we get all days Jan 1 of the next year.

The problem I am running into at the moment is having the formula understand that it cant change the numbers until the next year.

For example. Lets say the hire date was 12-20-17. This year I should still have 208 hours of PTO but as soon as tomorrow hits, it will give me 40 more hours.

For everyone's knowledge, this is just a prototype of something I may pitch to HR. It is not a big company or anything like that but thought I could reduce costs for us by making something simpler. I also love playing around with excel so thought why not. I would create a database for this if it was going to be a true idea but I like making things in excel first.

Thanks!
I hope you can go meta on this and realize why complicated Excel formulas are not that great for HR/Comp issues. The first issue is mistakes which you already made, the second is complexity. Your rules seem simple but as you start describing them it's not quite that simple after all since there are grant dates on Jan 1 and on certain threshold anniversaries, plus rollover limitations.

You can still do it in Excel (maybe), but I would stage your formula into a few columns and lookups. You can always hide columns with intermediate values but if you need to check or verify the formulas it's much easier to have each stage in its own column.

Are you trying to make a tool that helps HR/Finance? A tool that helps your co-workers? Are you just trying to illustrate a new compensation policy you are proposing?
I only thought of this today and was just playing around to see if I could come up with something better than what we currently have. I understand that there is a lot of complexity to this but was just starting with some of the basics. It is very much possible that I scratch this idea in a month or so if I cannot find a better solution.

My dad create databases for his job so I would learn from him on that and not have the final product be in excel but wanted to play around in excel first to see if this idea is even worth pursuing.

This took would only be for HR for PTO time. They spend what I consider too much money on a program to track PTO when the program has tons of features we will never use. It also may just be easier to look into purchasing something a lot cheaper to do this but I am hands on and want to see if I can create something first.

GrowthSeeker
Posts: 860
Joined: Tue May 15, 2018 10:14 pm

Re: Excel Formula Cannot Figure Out

Post by GrowthSeeker » Thu Dec 19, 2019 3:49 pm

Details of the rules will also matter.
Examples:
Q1: if someone first started work Aug 15, 2017 how many PTO hours did they get in 2017? Was it only 32, ie for the complete 4th quarter and none for Aug 15 to Sep 30? Or is it prorated and if prorated, how finely, ie nearest hour of PTO, nearest quarter hour of PTO, nearest full day of PTO??? Is what matters the number of full quarters they will work that year, or the number of full months, or pay periods or days?

Next detail.
Q2: I get that in Year 1 and 2 they get 128 hours per year; but when exactly does Year 3 begin?
So back to our friend who if someone first started work Aug 15, 2017: does year 3 begin Aug 15, 2019? Or Jan 1, 2020. Or Jan 2, 2019? And if the increase from 128/year to 168/year happens on the anniversary date of their hire, how is the partial month, partial quarter dealt with - see Q1.

If all details can't be clearly defined in text, then they can't be accurately represented by a spreadsheet or computer program.
Just because you're paranoid doesn't mean they're NOT out to get you.

User avatar
One Ping
Posts: 902
Joined: Thu Sep 24, 2015 4:53 pm

Re: Excel Formula Cannot Figure Out

Post by One Ping » Thu Dec 19, 2019 3:51 pm

mlipps wrote:
Thu Dec 19, 2019 12:21 pm
You don't need to nest the IF statement, you can instead use the function "IFS" to evaluate multiple if statements:

https://exceljet.net/excel-functions/excel-ifs-function
I did not know this! This is great! Thanks, mlipps! :beer
"Re-verify our range to target ... one ping only."

lotusflower
Posts: 288
Joined: Thu Oct 24, 2013 12:32 am

Re: Excel Formula Cannot Figure Out

Post by lotusflower » Thu Dec 19, 2019 4:49 pm

Mr400meterdash wrote:
Thu Dec 19, 2019 3:47 pm
This took would only be for HR for PTO time. They spend what I consider too much money on a program to track PTO when the program has tons of features we will never use. It also may just be easier to look into purchasing something a lot cheaper to do this but I am hands on and want to see if I can create something first.
Well I fully support learning and prototyping and figuring out Excel and databases, and saving the boss some money. Just be aware that whatever package HR is using has all kinds of features like reporting and security and audits and employee records privacy etc. Whatever arithmetic is being done is not why they pay for the software... because they could just do the arithmetic part in an Excel spreadsheet.

GrowthSeeker
Posts: 860
Joined: Tue May 15, 2018 10:14 pm

Re: Excel Formula Cannot Figure Out

Post by GrowthSeeker » Thu Dec 19, 2019 5:16 pm

I'm betting that a lot of people have trouble figuring out what the correct amount of PTO is. Maybe even the HR employees have a lot of trouble figuring it out. The HR software may be buggy. If any of these are true it might be because the rules are somewhat complicated an non-intuitive when comes down to specific details.
Just because you're paranoid doesn't mean they're NOT out to get you.

dbr
Posts: 32904
Joined: Sun Mar 04, 2007 9:50 am

Re: Excel Formula Cannot Figure Out

Post by dbr » Thu Dec 19, 2019 6:22 pm

Mr400meterdash wrote:
Thu Dec 19, 2019 3:47 pm


This took would only be for HR for PTO time. They spend what I consider too much money on a program to track PTO when the program has tons of features we will never use. It also may just be easier to look into purchasing something a lot cheaper to do this but I am hands on and want to see if I can create something first.
The first step in doing anything like this is to gather the requirements for the software and make a data model of the problem. You have to represent all the rules that apply. It could well be that all the mathematical work you need can be done in Excel, but it is unlikely you will arrive at a practical tool that can be shown to be error free while taking into account all the elements of a correct model. As mentioned, at the very least, there is going to have to be a confidential database of employee data.

You may be right that they are paying for a capability that exceeds their needs, but it may be worth it or it may be worthwhile shopping for a different model. Is it really true that the only job HR has for this tool is computing PTO entitlement, presumably for the purpose of advising employees of their entitlement and perhaps deciding if requested time off can be authorized.

ExitStageLeft
Posts: 1895
Joined: Sat Jan 20, 2018 4:02 pm

Re: Excel Formula Cannot Figure Out

Post by ExitStageLeft » Thu Dec 19, 2019 7:00 pm

lotusflower wrote:
Thu Dec 19, 2019 12:35 pm
Wish I worked at your company, 4380 hours is a lot of PTO!
You can nest logical IFs, but personally I think complicated Excel formulas are a big problem with Excel, and using a complicated formula for anything related to HR and comp puts a business at risk. Especially when you start copying formulas and it's easy to get relative vs absolute cell references wrong.

I would make a column that computes the number of years worked, and then I would make a table, maybe on another sheet, that has years of service in one column and PTO hours in the next column. Then you can use VLOOKUP in another column to pull the right pto value out of the table.
I echo this sentiment. VLOOKUP is nice in that it stops at the last row that matches the criterion. Here's a snippet using the OP's example:

Code: Select all

Starting date:	12/18/2011	
Today:		12/19/2019	=TODAY()
Years worked:	8.002777778	
PTO:		208		=VLOOKUP(B3,A8:B11,2,TRUE)
		
Leave Lookup Table		
Years	PTO Hours	
0	128	
3	168	
8	208	
12	248	
For a one-off calculation it's perhaps overkill but for a complicated set of tables or calculations it makes making future updates very clean.

User avatar
siamond
Posts: 5445
Joined: Mon May 28, 2012 5:50 am

Re: Excel Formula Cannot Figure Out

Post by siamond » Thu Dec 19, 2019 8:25 pm

OP, you received good answers, but next time, you may want to ask on a forum dedicated to Excel. I occasionally used this one with good results:
https://www.mrexcel.com/board/

Post Reply