"If" statements for multiple dates and date ranges that correspond to different formulas

**wls17** · 07-01-2022, 03:47 PM

I have a bunch of "If And" statements, as I want to apply a certain formula depending on date range (it's a cost forecasting model by month - so, there would be a certain formula if it's before a certain date, if it's between certain dates, and after a certain date). The formula works fine until I try "book-end" it with a simple "if" statement at the beginning and at the end (e.g., if the date is less than x, then it's y calculation; if the date is more than B date, then use C calc). When I add that logic in, I get a "#VALUE" message.

Below is the formula itself, and I have attached the spreadsheet for reference. Any help would be greatly appreciated!!

=IF(AND(S$30>=$D$30,S$30<$E$30),$M$32*$D32,IF(AND(S$30>=$E$30,S$30<$F$30),$M$32*$E32,IF(AND(S$30>=$F$30,S$30<$G$30),$M$32*$F32,IF(AND(S$30>=$G$30,S$30<$H$30),$M$32*$G32)*0)))

**etaf** · 07-01-2022, 04:22 PM

=IF(AND(N$6>=$B$6,N$6<$C$6),$I$8*$B8,IF(AND(N$6>=$C$6,N$6<$D$6),$I$8*$C8,IF(AND(N$6>=$D$6,N$6<$E$6),$I$8*$D8,IF(AND(N$6>=$E$6,N$6<$F$6),$I$8*$E8)*0))),IF(N6>F6,I8*F8,0) in spreadsheet

I breakdown into lines - in a notes document

=IF(
AND(N$6>=$B$6,N$6<$C$6),
$I$8*$B8,
IF(
AND(N$6>=$C$6,N$6<$D$6),
$I$8*$C8,
IF(
AND(N$6>=$D$6,N$6<$E$6),
$I$8*$D8,
IF(
AND(N$6>=$E$6,N$6<$F$6),
$I$8*$E8)*0

))),
IF(N6>F6,I8*F8,0)

So you have stopped the nested IF ???

is this what you want
=IF(AND(N$6>=$B$6,N$6<$C$6),$I$8*$B8,IF(AND(N$6>=$C$6,N$6<$D$6),$I$8*$C8,IF(AND(N$6>=$D$6,N$6<$E$6),$I$8*$D8,IF(AND(N$6>=$E$6,N$6<$F$6),$I$8*$E8*0,IF(N6>F6,I8*F8,0)))))

BUT
$I$8*$E8*0

will be zero - is that what you wanted

Just go back and cary on Nesting the IF's , and when finished add the final False and ))))))

should it be

=IF(AND(N$6>=$B$6,N$6<$C$6),$I$8*$B8,IF(AND(N$6>=$C$6,N$6<$D$6),$I$8*$C8,IF(AND(N$6>=$D$6,N$6<$E$6),$I$8*$D8,IF(AND(N$6>=$E$6,N$6<$F$6),$I$8*$E8,IF(N6>F6,I8*F8,0)))))

**wls17** · 07-01-2022, 04:56 PM

Thank you so much, that works great!

Quick other question -- if I wanted to pull the "Phase" inputs (columns B - F) from an assumptions sheet that had %s for various phases, employee types, and employee seniority levels all summarized, what formula would I use to pull them? I could link them manually, but feel like that's error-prone (e.g., pulling the % for Phase 1 for a director level employee etc. etc. )

**etaf** · 07-01-2022, 05:03 PM

sounds like a index/match or vlookup
But it would be better to start a new thread and have a sample sheet again for that specific question, so its new for everyone to see

you could always reply and add the link to the new post here , if you wanted

**wls17** · 07-01-2022, 05:18 PM

That makes sense.

I've reattached the file with an assumptions tab. I would be looking to populate the cells highlighted in yellow on the 2nd tab by pulling straight from the first based on a few criteria (employee title, date, and phase of project).

It seems like it would be an index/match situation, but I keep getting errors when I try.

**etaf** · 07-01-2022, 07:08 PM

i think this is what you are after
=INDEX(Assumptions!C$6:C$10,MATCH('Detail by Workstream'!$C32,Assumptions!$B$6:$B$10,0))

But no explanation, so just guessing

also i suggested stating a new question on the forum , rather than continue here with further replies for a different question

so if thats not correct , and does not answer the question, please do not continue here, but start a new thread / question in a new thread

"If" statements for multiple dates and date ranges that correspond to different formulas

LinkBack

Thread Tools

Rate This Thread

Display

"If" statements for multiple dates and date ranges that correspond to different formulas

Re: "If" statements for multiple dates and date ranges that correspond to different formul

Re: "If" statements for multiple dates and date ranges that correspond to different formul

Re: "If" statements for multiple dates and date ranges that correspond to different formul

Re: "If" statements for multiple dates and date ranges that correspond to different formul

Re: "If" statements for multiple dates and date ranges that correspond to different formul

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to Add Shape "LeftRightArrow", "Rectangle" & "Diamond" using Dates as Reference

Budget assist: Entering "Weekly" "Fortnightly" "Monthly" OR "Yearly" with formulas

[SOLVED] Need addtional criteria to count where "Actual" dates earlier that "Scheduled" DATE

Using "index", "match", "min", "abs", to find the closest value in multiple criteria

Filter by "Confirmed" and by "Date (confirm or order)" copy and paste to "Invoice Audit"

Need some help with "nested if statements function" & "classifiying the group category"

Excel 2007 : If Statements to return "-" if no dates exists

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions