Advanced Formula - If statement + weeks in months - Impossible?

[natheplas]

Hi,

I've put together this formula.

=IF([@[NOV-17 Finished Goods SO Demand]]>[@[VARIABLE SO Months Average]], ([@[QTY IN STOCK]]+[@[QTY ON ORDER NOV 17]])-[@[NOV-17 Finished Goods SO Demand]], ([@[QTY IN STOCK]]+[@[QTY ON ORDER NOV 17]])-[@[VARIABLE SO Months Average]])

It works, but I need to take it a step further.

Currently the formula looks at two numbers on the same row and which ever one is greater, it will use that number.

The 'VARIABLE SO Months Average' is 3 months of sales order history /3.

This therefore would be overstating the stock quantity if I use this if we are on the last week of the month.

Is it at all possible using a today formula or week, or EO etc, to get the formula above to change the VARIABLE SO Months Average depending on what week we are in of the month.

For example if I opened the spreadsheet up on the 1st of the month it would be the same as the 'VARIABLE SO Months Average' would be for the whole month, 4 weeks worth (= VARIABLE SO Months Average / 4 * 4) and then compare against 'Finished Goods SO Demand'

However, if I opened the spreadsheet up on the 28th of the month, that is the last week of the month, so it would only be 1 weeks worth.
= VARIABLE SO Months Average / 4 * 1

I know what I need to achieve, but I don't know if it's possible to put it in the above formula so the sum changes depending on what week we are in of the month.

If anyone can help with this impossible task it would be a massive help!

 

[mrshl9898]

To work out how many weeks from today til the end of month, you can use this:

=(EOMONTH(TODAY(),0)-TODAY())/7

Maybe add a roundup if you are after the whole number:

=ROUNDUP((EOMONTH(TODAY(),0)-TODAY())/7,0)

 

[natheplas]

Hi mrshl9898,

That's great, thank you for the formulas.

So if I use what you've given to me above and go...

=IF([@[NOV-17 Finished Goods SO Demand]]/4*(=ROUNDUP((EOMONTH(TODAY(),0)-TODAY())/7,0))>[@[VARIABLE SO Months Average]], ([@[QTY IN STOCK]]+[@[QTY ON ORDER NOV 17]])-([@[NOV-17 Finished Goods SO Demand]]/4*(=ROUNDUP((EOMONTH(TODAY(),0)-TODAY())/7,0)), ([@[QTY IN STOCK]]+[@[QTY ON ORDER NOV 17]])-[@[VARIABLE SO Months Average]])

I know that's not going to work, but that is what I'm trying to achieve.

Can you help me please put the above formula together so it works?

Not sure how to join what I've done and what you've given me.

 

[mrshl9898]

That looks pretty close, try:

=IF([@[NOV-17 Finished Goods SO Demand]]/4*(ROUNDUP((EOMONTH(TODAY(),0)-TODAY())/7,0))>[@[VARIABLE SO Months Average]], ([@[QTY IN STOCK]]+[@[QTY ON ORDER NOV 17]])-([@[NOV-17 Finished Goods SO Demand]]/4*(ROUNDUP((EOMONTH(TODAY(),0)-TODAY())/7,0)), ([@[QTY IN STOCK]]+[@[QTY ON ORDER NOV 17]])-[@[VARIABLE SO Months Average]])

I just removed the "=".

 

[natheplas]

Hi,

Thank you, that's a lot better than what I just put together. I added a column and * by the relevant cell.

Thank you!