Home

A Formula For This?

%3CLINGO-SUB%20id%3D%22lingo-sub-863988%22%20slang%3D%22en-US%22%3EA%20Formula%20For%20This%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-863988%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20guys%2C%3CBR%20%2F%3E%3CBR%20%2F%3ENot%20too%20good%20with%20excel%2C%20but%20I%20have%20an%20odd%20question%20about%20formulas.%20I%20will%20try%20to%20explain%20this%20the%20best%20I%20can.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20was%20wondering%20if%20there%20is%20a%20formula%20for%20a%20situation%20like%20this%3A%20Imagine%20an%20inventory%20report%3A%20if%20Column%20C%20is%20correct%2C%20Column%20D%20has%20a%20checkbox%20in%20it%2C%20Column%20E%20would%20reflect%20Column%20C.%20If%20the%20inventory%20in%20Column%20C%20is%20correct%2C%20you%20check%20the%20box%20in%20Column%20D%20and%20then%20it%20would%20reflect%20Column%20C's%20value%20in%20Column%20E.%20If%20it%20isn't%20correct%2C%20in%20Column%20E%2C%20you'd%20type%20in%20the%20value%20that%20was%20counted%20in%20inventory%20and%20Column%20F%20would%20have%20the%20difference%20between%20Column%20E%20and%20Column%20C%20to%20show%20that%20it%20was%20incorrect%20in%20inventory%20and%20the%20difference%20being%20in%20column%20F%20is%20how%20much%20the%20inventory%20is%20off%20by%20%2B%2F-.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-863988%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-864007%22%20slang%3D%22en-US%22%3ERe%3A%20A%20Formula%20For%20This%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-864007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F412073%22%20target%3D%22_blank%22%3E%40Skovoska%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20have%20the%20suspected%20inventory%20on%20hand%20be%20column%20C.%26nbsp%3B%20I'm%20guessing%20you%20want%20Column%20D%20because%20you%20don't%20want%20the%20inventory%20person%20to%20have%20to%20enter%20by%20hand%20the%20actual%20number%20for%20each%20row%2C%20and%20short%20circuit%20is%20to%20check%20a%20box%20if%20they%20match.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimple%20way%20to%20do%20this%20would%20be%20to%20have%20column%20D%20be%20a%20%22Match%20expected%3F%22%20header%20and%20just%20put%20a%20%22Y%22%20or%20%22N%22%20in%20the%20column%20to%20indicate%20yes%20or%20no.%26nbsp%3B%20(%20or%20just%20Y%20for%20yes%2C%20blank%20is%20implied%20no%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20E%20is%20the%20actual%20inventory%20on%20hand%20(which%20if%20you%20enter%20all%20the%20actuals%2C%20then%20you%20can%20simply%20check%20E%20vs%20C%20for%20variance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20you%20can%20have%20a%20formula%20like%20this%20for%20column%20F%3A%3C%2FP%3E%3CP%3E%3Dif(%24D1%3D%22Y%22%2C%24C1%2CE1-C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20color%20format%20column%20F%20for%20Green%2FRed%20by%20variance%20threshold%20etc...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20don't%20want%20to%20do%20the%20%22Y%22%20%2F%20%22N%22%2C%20you%20could%20do%20a%20box%20click%2C%20but%20would%20be%20more%20involved%20to%20make%20that%20work%2C%20and%20would%20be%20VB%20coding%20as%20well%20to%20align.%26nbsp%3B%20Above%20is%20simpler%20IMHO.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Skovoska
Occasional Visitor

Hey guys,

Not too good with excel, but I have an odd question about formulas. I will try to explain this the best I can.

I was wondering if there is a formula for a situation like this: Imagine an inventory report: if Column C is correct, Column D has a checkbox in it, Column E would reflect Column C. If the inventory in Column C is correct, you check the box in Column D and then it would reflect Column C's value in Column E. If it isn't correct, in Column E, you'd type in the value that was counted in inventory and Column F would have the difference between Column E and Column C to show that it was incorrect in inventory and the difference being in column F is how much the inventory is off by +/-. 

Thanks in advance!

1 Reply

@Skovoska 

 

You can have the suspected inventory on hand be column C.  I'm guessing you want Column D because you don't want the inventory person to have to enter by hand the actual number for each row, and short circuit is to check a box if they match.  

 

Simple way to do this would be to have column D be a "Match expected?" header and just put a "Y" or "N" in the column to indicate yes or no.  ( or just Y for yes, blank is implied no )

 

Column E is the actual inventory on hand (which if you enter all the actuals, then you can simply check E vs C for variance.

 

Then you can have a formula like this for column F:

=if($D1="Y",$C1,E1-C1)

 

You can color format column F for Green/Red by variance threshold etc...

 

If you don't want to do the "Y" / "N", you could do a box click, but would be more involved to make that work, and would be VB coding as well to align.  Above is simpler IMHO.

 

hope this helps.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies