Nested IF formula

[REE13]

[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>

 

[REE13]

Re: I need a nested IF formula (help)

Yes, B4 would be the date the item is placed on the shelf. Thank you for asking that clarifying question.

 

[KolGuyXcel]

Re: I need a nested IF formula (help)

Okay.

Now, we are dealing with 2 different attributes of an item.

1. No of weeks the item is on display.
2. Whether the item had been accounted for during the last review.

2 different attributes can be displayed in 2 cells. No of weeks of display with the DATEDIF formula in B7. Whether accounted for during the last review in another cell, say B8, using my earlier formula, modified a bit.
=IF(B4 > B3,"NEW","ACCOUNTED") or =IF(B4 > B3,"NEW","")

Both these can be displayed on cell B7 using the following formula, however it may strip off the numerical property of the display and complicate further numerical operations based on the number of weeks on display!

=IF(B4 > B3,"NEW","ACCOUNTED")&" "&DATEDIF(B6,IF(B5="",B3,B5),"d")/7

or

=TRIM(IF(B4 > B3,"NEW","")&" "&DATEDIF(B6,IF(B5="",B3,B5),"d")/7)

 

[James006]

Re: I need a nested IF formula (help)

Hi Rachel,

Word should be totally banned from this planet ...!!!

(Unless, you are typing letters the whole day long ...)

Please stick to Excel ...

You will make your life so much easier ...

As an example, the problem you have to solve ....

can ONLY be solved with Excel ... !!!

Take Care

 

[James006]

Re: I need a nested IF formula (help)

If we want to clarify your rules, let's deal with the First date and then with the End date ...

A. First date :

This start date can be B4 or B5 or B6 ...

What are the rules for this specific portion ?

B. End date

This end date can be B3 or B4 or B5 ...

What are the rules for this specific portion ?

Hope this clarifies

 

[James006]

Re: I need a nested IF formula (help)

Based on your Word table ... which is structured totally differently ...

For cell F2 ...

=DATEDIF(IF(C2 < E2,E2,C2),IF(D2="",B2,D2),"d")/7

<e2,e2,c2),if(d2="",b2,d2),"d") 7[="" code]

HTH</e2,e2,c2),if(d2="",b2,d2),"d")>

 

[REE13]

Re: I need a nested IF formula (help)

Thank you. Sorry for the offensive word.. used so I could add an explanatory paragraph (thinking now I could have just merged a cell, lol. Also different table as I figured it would be more self explanatory if I laid it out in a much easier to grasp manner as actual example just seemed confusing.. so "dummy" table created for ease of answer.

 

[James006]

Re: I need a nested IF formula (help)

Hello,

Should the formula posted in message # 15 not produce your expected result ...

Please take the time to review the message # 14 ... in order to come up with clear rules for both your dates ...:wink: