Formula Help - Create formula based on last column entry

%3CLINGO-SUB%20id%3D%22lingo-sub-1516063%22%20slang%3D%22en-US%22%3EFormula%20Help%20-%20Create%20formula%20based%20on%20last%20column%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516063%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EApologies%20if%20this%20query%20has%20come%20up%20previously..%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20simple%20formula%20in%20column%20B%20that%20uses%20the%20latest%20column%20of%20filled%20data%20(i.e.%20column%20E%20in%20the%20screenshot%20below)%20and%20then%20calculates%20the%20proportion%20(allocation%20%25)%20of%20each%20stock%20item%20against%20the%20total%20of%20all%20stock%20items.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20instance%2C%20cell%20B3%20would%20currently%20calculate%20as%20E3%2FE6*100.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20data%20for%20next%20month%20is%20entered%20in%20column%20F%2C%20then%20the%20formula%20would%20then%20use%20this%20data%20(i.e.%20F3%2FF6*100)%20and%20so%20on%20for%20subsequent%20columns%20filled.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22nph20_1-1594446586349.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204754i8EA105AAD147B4CB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22nph20_1-1594446586349.png%22%20alt%3D%22nph20_1-1594446586349.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAny%20advice%20much%20appreciated!%3CBR%20%2F%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1516063%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-1516095%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20-%20Create%20formula%20based%20on%20last%20column%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516095%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725481%22%20target%3D%22_blank%22%3E%40nph20%3C%2FA%3E%26nbsp%3BMade%20easier%20if%20you%20could%20break-out%20the%20Allocation%25%20calculation%20from%20rows%20with%20stock%20quantities%2C%20as%20demonstrated%20in%20the%20attached%20workbook.%20Though%2C%20I%20would%20not%20call%20the%20formula%20%22simple%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516362%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20-%20Create%20formula%20based%20on%20last%20column%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725481%22%20target%3D%22_blank%22%3E%40nph20%3C%2FA%3E%26nbsp%3B%2C%20A%20variation%20using%20INDEX%20and%20MATCH%20using%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bworkbook.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516379%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20-%20Create%20formula%20based%20on%20last%20column%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516379%22%20slang%3D%22en-US%22%3E%3CP%3EOne%20other%20way%20I%20believe%20you%20could%20do%20it%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3D100*LOOKUP(MAX(C3%3AXFD3)%2B1%2CC3%3AXFD3)%2FLOOKUP(MAX(C3%3AXFD3)%2B1%2CC3%3AXFD3%2CC%246%3AXFD%246)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20I%20don't%20know%20how%20far%20your%20data%20will%20extend%20to%20the%20right%2C%20I%20went%20all%20the%20way%20to%20the%20last%20column%20(XFD).%20If%20your%20table%20has%20a%20definite%20boundary%2C%20then%20use%20that%20column%20instead.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1520577%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20-%20Create%20formula%20based%20on%20last%20column%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1520577%22%20slang%3D%22en-US%22%3E%3CP%3EPerfect%20-%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi all,

Apologies if this query has come up previously..

I am trying to create a simple formula in column B that uses the latest column of filled data (i.e. column E in the screenshot below) and then calculates the proportion (allocation %) of each stock item against the total of all stock items.

 

So for instance, cell B3 would currently calculate as E3/E6*100.

 

Once data for next month is entered in column F, then the formula would then use this data (i.e. F3/F6*100) and so on for subsequent columns filled.

 

nph20_1-1594446586349.png

Any advice much appreciated!
Thanks.

 

4 Replies
Highlighted

@nph20 Made easier if you could break-out the Allocation% calculation from rows with stock quantities, as demonstrated in the attached workbook. Though, I would not call the formula "simple".

Highlighted

@nph20 , A variation using INDEX and MATCH using @Riny_van_Eekelen workbook. 

Highlighted

One other way I believe you could do it:

=100*LOOKUP(MAX(C3:XFD3)+1,C3:XFD3)/LOOKUP(MAX(C3:XFD3)+1,C3:XFD3,C$6:XFD$6)

 

Since I don't know how far your data will extend to the right, I went all the way to the last column (XFD). If your table has a definite boundary, then use that column instead.

Highlighted

Perfect - thanks!