Nested IF formula

R

REE13

New Member
Joined
Nov 13, 2018
Messages
10
[TABLE="width: 300"]
<tbody>[TR]
[TD]1
[/TD]
[TD="align: center"] A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Date fie Reviewed
[/TD]
[TD]11/30/2018
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]First Date of Item Entry
[/TD]
[TD]12/15/2014
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Last Stock Date
[/TD]
[TD]9/18/2016
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Final Inv. Date
[/TD]
[TD]11/30/2014
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Weeks on Promo Disp.
[/TD]
[TD]90.43
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am trying to figure out a way to turn this into an automatic spreadsheet/calculator function and am failing. I want to automate the "B7" cell.

I recreated best I could showing the follow parameters:


1. Date of First Item received (B4)
2. Date it was removed from shelf (B5) *note there needs to be the flexibility to have this column be blank if it's still on shelf
3. If the date it was removed (B5) was before final invoicing date (B6) than it's note relevant to what I am doing and can be marked "NA"
4. If item is still on shelf and B5 is going to be blank than date filed reviewed B3 will be used to calculate the # of weeks on display.


I tried this:


=IF(B5<b6),"na",if(b4>B6,AND(B5<1),(B3-B4)/7)),IF(B4>B6,AND(B5>1),(B5-B4)/7))


Didn't work at all...



I have tried many different iterations of the above (using IF, AND, and OR) and I can't do it..... ideas?

If you can follow the logic, I am trying to get weeks on the shelf.

Asking from today to Date first Received (# of weeks on the shelf)
Also - If removed early (B5) from date received to date removed BUT
If received before B6.. not interested.

Any help. Thanks!</b6),"na",if(b4>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Re: I need a nested IF formula (help)

Hello,

You could test following

Code: 
=DATEDIF(B6,IF(B5="",B3,B5),"d")/7

HTH
 
Upvote 0
Re: I need a nested IF formula (help)

It works!!! Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 
Upvote 0
Re: I need a nested IF formula (help)

You are welcome ... :smile:
 
Upvote 0
Re: I need a nested IF formula (help)

I have a follow-up question. Is there are way to add/change the formula to account for products added to shelves after last review period?

[TABLE="class: cms_table, width: 300"]
<tbody>[TR]
[TD]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Date fie Reviewed[/TD]
[TD]11/30/2018[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]First Date of Item Entry[/TD]
[TD]12/15/2014[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Last Stock Date[/TD]
[TD]9/18/2016[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Final Inv. Date[/TD]
[TD]11/30/2014[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Weeks on Promo Disp.[/TD]
[TD]90.43[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am trying to figure out a way to turn this into an automatic spreadsheet/calculator function and am failing. I want to automate the "B7" cell.

I recreated best I could showing the follow parameters:


1. Date of First Item received (B4)
2. Date it was removed from shelf (B5) *note there needs to be the flexibility to have this column be blank if it's still on shelf
3. If the date it was removed (B5) was before final invoicing date (B6) than it's note relevant to what I am doing and can be marked "NA"
4. If item is still on shelf and B5 is going to be blank than date filed reviewed B3 will be used to calculate the # of weeks on display.

5. If Item b3 is dated earlier than Item b4????

That way we can track newly added since last accounting as well.

Ideas? The formula (although brilliant) doesn't account and my tweaking isn't helping.
 
Last edited:
Upvote 0
Re: I need a nested IF formula (help)

I'm not sure if I've understood the follow-up question, but we can give this a try!

=IF(B4<B3,"NEW",DATEDIF(B6,IF(B5="",B3,B5),"d")/7)<b3,"new",datedif(b6,if(b5="",b3,b5),"d") 7)<="" html=""><b3,"new",< b3,"new",datedif(b6,if(b5="" ,b3,b5),"d")=""><b3,"new",datedif(b6,if(b5="",b3,b5),"d") 7)<="" b3,"new",<=""></b3,"new",datedif(b6,if(b5="",b3,b5),"d")></b3,"new",<></b3,"new",datedif(b6,if(b5="",b3,b5),"d")>
 
Last edited:
Upvote 0
Re: I need a nested IF formula (help)

I don't know why this page isn't displaying the complete formula -

=IF(B4 < B3,"NEW",DATEDIF(B6,IF(B5="",B3,B5),"d")/7)
 
Upvote 0
Re: I need a nested IF formula (help)

Unfortunately that doesn't work... it only ends up saying "NEW" in the cell.
 
Upvote 0
Re: I need a nested IF formula (help)

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD]Add’l Information/Parameters[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]Date file Reviewed
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]11/30/2018[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]B3 is actually the date of last inventory taken. This date can be earlier than “First Date of Item Entry” as well as later.
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]First Date of Item Entry[/TD]
[TD]12/15/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]Last Stock Date
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]9/18/2016[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]B5 Needs to be flexible as this column may be blank if it's still on shelf
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Final Inv. Date[/TD]
[TD]11/30/2014[/TD]
[TD]B6 would only be used in the formula is B5 is blank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Weeks on Promo Disp.[/TD]
[TD]??[/TD]
[TD]B7 is the number of weeks the item is on display. It is what I trying to figure out, lol![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The problem is not the formula but my explanation. I am trying to do better. I think the formula received makes sense to answer what you thought was asking… hopefully this is a much better explanation… I am baby-stepping my way there…
 
Upvote 0
Re: I need a nested IF formula (help)

If B3 is earlier than B4 should we consider B4 as the date the item is put up on display on shelf? Or how is the date the item put up on display co-related to the dates in cells B3 and B4?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Deepakchoudhary
  • Solved
Weekday marking based on a date in during current week
Replies
2
Views
289
Deepakchoudhary
Deepakchoudhary
D
  • Solved
How To Adjust Nested IFS Formula To Include An Additional Column's Condition?
Replies
5
Views
309
Fluff
Fluff
Nlhicks
  • Solved
Need Conditional Formatting Formula to Change the Highlight to Red if today is more than 5 years past sticker date and yellow if it is 4 years past
2
Replies
10
Views
222
etaf
etaf
J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
260
Alex Blakenburg
A
I
  • Question Question
Accrual Worksheet Next Pay date Formula
Replies
0
Views
266
ItalianPlatinum
I

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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