Excel formulas/function help

%3CLINGO-SUB%20id%3D%22lingo-sub-2179966%22%20slang%3D%22en-US%22%3EExcel%20formulas%2Ffunction%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2179966%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20trying%20to%20do%20an%20analysis%20to%20determine%20what%20is%20the%20best%20order%20qty%20and%20pricing%20considering%20inventory%20carrying%20cost.%20I%20put%20together%20an%20example%20but%20my%20file%20has%26nbsp%3B%20more%20than%20200%20lines.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20table%20has%20my%20source%20data%20and%20calculation%20to%20determine%20what%20is%20the%20best%20order%20qty%20and%20pricing.%3C%2FP%3E%3CP%3ETake%20PN%201%20for%20example%2C%20it%20is%20best%20for%20me%20to%20order%20at%20300%20units%20at%20pricing%20of%200.6.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22259%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2230%22%3EPN%3C%2FTD%3E%3CTD%20width%3D%2297%22%3EOrder%20Qty%3C%2FTD%3E%3CTD%20width%3D%2235%22%3EPrice%3C%2FTD%3E%3CTD%20width%3D%2297%22%3EBest%20Order%20Qty%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN1%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E0.5%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN1%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E0.6%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN1%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E0.7%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN2%3C%2FTD%3E%3CTD%3E150%3C%2FTD%3E%3CTD%3E0.1%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN2%3C%2FTD%3E%3CTD%3E250%3C%2FTD%3E%3CTD%3E0.2%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN2%3C%2FTD%3E%3CTD%3E350%3C%2FTD%3E%3CTD%3E0.3%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN2%3C%2FTD%3E%3CTD%3E450%3C%2FTD%3E%3CTD%3E0.4%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN3%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3CTD%3E1.5%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN3%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E1.6%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN3%3C%2FTD%3E%3CTD%3E150%3C%2FTD%3E%3CTD%3E1.7%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN3%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E1.8%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20know%20how%20to%20write%20a%20formulate%20using%20above%20input%20to%20spit%20out%20a%20table%20below.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22162%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2230%22%3EPN%3C%2FTD%3E%3CTD%20width%3D%2297%22%3EBest%20Order%20Qty%3C%2FTD%3E%3CTD%20width%3D%2235%22%3EPrice%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN1%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E0.6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN2%3C%2FTD%3E%3CTD%3E250%3C%2FTD%3E%3CTD%3E0.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPN3%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3CTD%3E1.5%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help%2Ftip!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2179966%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2180875%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%2Ffunction%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2180875%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F984175%22%20target%3D%22_blank%22%3E%40Jade_Goard%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20this%20%2C%2C%20has%20formula%20in%20cell%20D3%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(AND(B3%3D%24F%242%2CC3%26gt%3B%3D%24F%243)%2C%22Yes%22%2C%22No%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20array%20(CSE)%20formula%20in%20cell%20H3%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7B%3DIFERROR(INDEX(A%243%3AA%2413%2C%20SMALL(IF(%24D%243%3A%24D%2413%3D%22Yes%22%2C%20ROW(%24D%243%3A%24D%2413)-ROW(%24D%243)%2B1)%2C%20ROW(1%3A1)))%2C%22%22%20)%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EFinish%20formula%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%2C%20and%20fill%20across%20the%20range.%3C%2FLI%3E%3CLI%3EAdjust%20cell%20references%20in%20the%20formula%20as%20needed%2C%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2185638%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%2Ffunction%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2185638%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3Bthank%20you%20very%20much.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edoes%20this%20mean%20to%20calculate%20values%20in%20D%20(best%20order%20qty)%2C%20I%20need%20to%20adjust%20F2%20and%20F3%2C%20as%20each%20PN%20have%20different%20pricing%20tiers%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20something%20I%20hope%20to%20shy%20away%20as%20my%20actual%20file%20has%20maybe%20100-200%20unique%20PNs%20and%20each%20PN%20has%203-6%20price%20tiers.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I am trying to do an analysis to determine what is the best order qty and pricing considering inventory carrying cost. I put together an example but my file has  more than 200 lines.

 

This table has my source data and calculation to determine what is the best order qty and pricing.

Take PN 1 for example, it is best for me to order at 300 units at pricing of 0.6.

 

PNOrder QtyPriceBest Order Qty
PN11000.5Yes
PN12000.6Yes
PN13000.7No
PN21500.1Yes
PN22500.2Yes
PN23500.3No
PN24500.4No
PN3501.5Yes
PN31001.6No
PN31501.7No
PN32001.8No

 

I would like to know how to write a formulate using above input to spit out a table below. 

 

PNBest Order QtyPrice
PN12000.6
PN22500.2
PN3501.5

 

Thank you for any help/tip!

3 Replies

@Jade_Goard 

 

Check this ,, has formula in cell D3:

=IF(AND(B3=$F$2,C3>=$F$3),"Yes","No")

 

An array (CSE) formula in cell H3:

{=IFERROR(INDEX(A$3:A$13, SMALL(IF($D$3:$D$13="Yes", ROW($D$3:$D$13)-ROW($D$3)+1), ROW(1:1))),"" )}

 

  • Finish formula with Ctrl+Shift+Enter, and fill across the range.
  • Adjust cell references in the formula as needed,

 

@Rajesh-S thank you very much.

 

does this mean to calculate values in D (best order qty), I need to adjust F2 and F3, as each PN have different pricing tiers?

 

That is something I hope to shy away as my actual file has maybe 100-200 unique PNs and each PN has 3-6 price tiers. 

@Jade_Goard 

Simplest way is to use Power Query if you consider such option - filter on Best Order; sort by PN (ascending) and Price (descending), remove duplicates.