inventory database

[Ganolean]

Hi!
Can anyone help me with a formula for my inventory database that will tell it to check the current stock off an item vs the day it actually is and how much stock of an item is supposed to be on hand then return the qty needed to build to that amount.

For example under Neopolitan Crust the current stock is 2 so in the need prepare column it will check that amount vs the days build qty so it would check what day it is and on saturday it says the min is 3 and max is 6 so since the current stock is less then the minimum build qty we must make 4 more to build to max however if the current stock was equal to the minimum build qty you would make nothing

this formula returns and does the math exactly right but because the days change I need to logically test what day it is first and only return the value based on the day
=IF(E7>=P7,0,Q7-E7)

[Phuocam]

Try this:

=IF(E7>MAXIFS(F7:S7,$F$6:$S$6,"MIN"),0,MAXIFS(F7:S7,$F$6:$S$6,"MAX")-E7)

or:

=IFERROR(IF(LOOKUP(10^35,F7:S7/(F7:S7<>"")/($F$6:$S$6="MIN"))>E7,LOOKUP(10^35,F7:S7/(F7:S7<>"")/($F$6:$S$6="MAX"))-E7,""),"")

[Ganolean]

I dont think this checks the day first, for example if its monday I need it to check the min max from monday columns and if its saturday it should check the saturday min max columns as they will have differant numbers

[Phuocam]

Maybe like this:

=IFERROR(IF(E7>INDEX(F7:S7,MATCH(TRUE,F7:S7<>"",0)),0,INDEX(F7:S7,MATCH(TRUE,F7:S7<>"",0)+1)-E7),"")

[Ganolean]

This does not reference the day either.. The actual day is displayed in cell B3 on the worksheet so the formula has to find the min max columns for the corresponding day which is displayed in columns F5:R5 and then be able to do the math in those column once it determines which day to pick from

[Phuocam]

=IFERROR(IF(E7>INDEX(F7:S7,MATCH($B$3,$F$5:$S$5,0)),0,INDEX(F7:S7,MATCH($B$3,$F$5:$S$5,0)+1)-e7),"")

[Ganolean]

Almost but no when you change the day the math is off

[janmorris]

although the example spreadsheet provided looks pretty, i question if it would be practical to use in the real world.

1. this may be useful for a workforce that has learning difficulties with simple mathematics, but they most likely also will not be able to use the computer device to enter the required data.
2. in a real world kitchen, staff do not usually walk from fridge to fridge and enter amounts into a spreadsheet then go and print it every day off so they can actually do the prep, rather they would use a standard sheet that is printed once a week and then fill it in every day, this also provides a hard copy record for other uses (management data entry, analysis etc)
3. it does not offer a way to detect if prep has actually been done on the displayed day
4. it assumes enough inventory is always on hand to meet the PAR level requirements

so, based on that input, here is a revised structure that could resolve these issues by:

1. easy to read tables for simple mathematics
2. simple printable sheet for 1 (period ending) week.
3. staff can mark when prep is done
4. staff can highlight when inventory is deficient and explain to management why less than par was actually produced

[menem]

Please try D7

=IF(E7 < INDEX($F7:$S7,1,(((MOD(INT($B$2),7)-1)+IF(MOD(INT($B$2),7)-1 < 1,7,0))-1)*2+1),
INDEX($F7:$S7,1,(((MOD(INT($B$2),7)-1)+IF(MOD(INT($B$2),7)-1 < 1,7,0))-1)*2+2)-E7,0)

Regards.