Nested Ifs function with date and calculation

Rcaaa

New Member
Joined
May 21, 2018
Messages
22
=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?

C
D
E
FG
H
2Project NameXYZYearMonth
3Start Date2/5/1920191
4Completion Date12/5/20202012
5202112
6
7
8FY Budget2019$400,000
9FY Budget2020$200,000
10FY Budget2021$100,000
Month
Planned Cost
12/5/19This is where the formula would go
1/5/20

<tbody>
</tbody>
 
Last edited:

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
302
=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
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
302
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))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,736
Office Version
2007
Platform
Windows
=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
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
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
302
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,736
Office Version
2007
Platform
Windows
All the time we are learning, and especially in excel ;)
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
302
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,736
Office Version
2007
Platform
Windows
=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?

C
D
E
FG
H
2Project NameXYZYearMonth
3Start Date2/5/1920191
4Completion Date12/5/20202012
5202112
6
7
8FY Budget2019$400,000
9FY Budget2020$200,000
10FY Budget2021$100,000
Month
Planned Cost
1412/5/19This is where the formula would go
1/5/20

<tbody>
</tbody>
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?
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
302
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))
 

Rcaaa

New Member
Joined
May 21, 2018
Messages
22
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
 

Forum statistics

Threads
1,081,676
Messages
5,360,441
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top