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

@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.