Nested Ifs function with date and calculation

R

Rcaaa

New Member
Joined
May 21, 2018
Messages
23
=IFS(C14=YEAR(D8),(E8/H3),C14=YEAR(D9),(E9/H4),C14=YEAR(D10),(E10/H5))


Above is my formula that is not working.
I am trying to get a spreadsheet that automatically distributes the budget evenly based on each year by month. Does anyone have advice on how to correct my formula?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Project Name[/TD]
[TD][/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD]Year[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Start Date[/TD]
[TD][/TD]
[TD]2/5/19[/TD]
[TD][/TD]
[TD]2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Completion Date[/TD]
[TD][/TD]
[TD]12/5/20[/TD]
[TD][/TD]
[TD]2020[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2021[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]FY Budget[/TD]
[TD]2019[/TD]
[TD]$400,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]FY Budget[/TD]
[TD]2020[/TD]
[TD]$200,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]FY Budget[/TD]
[TD]2021[/TD]
[TD]$100,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Month
[/TD]
[TD]Planned Cost
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12/5/19[/TD]
[TD]This is where the formula would go[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/5/20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
=IF(C14=YEAR(D8),(E8/H3),IF(C14=YEAR(D9),(E9/H4),IF(C14=YEAR(D10),(E10/H5),"")))

IFS is not a formula. If you want a nested if, in the "if false" section you need to put IF(logic test, if true, if false)

The formula I provided should give you a blank cell if it does not equal any of those cells
 
Upvote 0
If it has to be one of those cells, this formula will also work for you

=IF(C14=YEAR(D8),(E8/H3),IF(C14=YEAR(D9),(E9/H4),E10/H5))
 
Upvote 0
jondavis1987 said:
=IF(C14=YEAR(D8),(E8/H3),IF(C14=YEAR(D9),(E9/H4),IF(C14=YEAR(D10),(E10/H5),"")))

IFS is not a formula. If you want a nested if, in the "if false" section you need to put IF(logic test, if true, if false)

The formula I provided should give you a blank cell if it does not equal any of those cells
Click to expand...

IFS function
Excel for Office 365 Excel for Office 365 for Mac Excel 2019 Excel 2016 More...


The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.

https://support.office.com/en-us/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45
 
Upvote 0
Well, learn something new everyday. I've only got the 2013 version. A better nested if function would have been nice a year ago when i was setting stuff up
 
Upvote 0
Well, I don't know how to give you an ifs formula since i can't play with it, but the one i provided should work.
 
Upvote 0
Rcaaa said:
=IFS(C14=YEAR(D8),(E8/H3),C14=YEAR(D9),(E9/H4),C14=YEAR(D10),(E10/H5))


Above is my formula that is not working.
I am trying to get a spreadsheet that automatically distributes the budget evenly based on each year by month. Does anyone have advice on how to correct my formula?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Project Name[/TD]
[TD][/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD]Year[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Start Date[/TD]
[TD][/TD]
[TD]2/5/19[/TD]
[TD][/TD]
[TD]2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Completion Date[/TD]
[TD][/TD]
[TD]12/5/20[/TD]
[TD][/TD]
[TD]2020[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2021[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]FY Budget[/TD]
[TD]2019[/TD]
[TD]$400,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]FY Budget[/TD]
[TD]2020[/TD]
[TD]$200,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]FY Budget[/TD]
[TD]2021[/TD]
[TD]$100,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Month
[/TD]
[TD]Planned Cost
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]12/5/19[/TD]
[TD]This is where the formula would go[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/5/20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

But in C14 do you have a date?
In D8 you only have the year?

Then
This:
C14=YEAR(D8)

Should be:
YEAR(C14) = D8

Do you need to compare cell by cell or rather look for the year of C14 in a range of cells D8: D10 and get the value of column E?
 
Upvote 0
Going off his table and the labels, D8 is a date. I didn't realize he's also got dates where C14 should go. If dates are in C14, this is the way to go

=IF(YEAR(C14)=YEAR(D8),(E8/H3),IF(YEAR(C14)=YEAR(D9),(E9/H4),IF(YEAR(C14)=YEAR(D10),(E10/H5),"")))

or in your original format

=IFS(YEAR(C14)=YEAR(D8),(E8/H3),YEAR(C14)=YEAR(D9),(E9/H4),YEAR(C14)=YEAR(D10),(E10/H5))
 
Upvote 0
Hello,

Thank you both for your help. It is working now!

Yes, I have a date in C14 (Now D14 after adding in a column to separate the year from the 12/5/19 date)
I also created a column with a monthly budget rate instead of dividing it in the formula(400,000/1 month - AKA E8/H3).


=IF($D14=($E$8),($J$3),IF($D14=($E$9),($J$4),IF($D14=($E$10),($J$5),"")))

Thanks again,

RCAAA
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
How to create monthly report for activities based on created date, closed date, and due date, for past year?
Replies
1
Views
468
josevaldesv1
J
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
210
Tetra201
Tetra201
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
159
ellison
E
S
  • Question Question
Using IFS to calculate a task due by date but needing to remove weekends
Replies
3
Views
474
awoohaw
awoohaw
LearningByDoing
  • Solved
No correct calculation with Nested IF + AND Function
Replies
4
Views
123
Alex Blakenburg
A

Forum statistics

Threads
1,224,815
Messages
6,181,135
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top