Formula Help - Create formula based on last column entry

[nph20]

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.

Any advice much appreciated!
Thanks.

[#2]

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

[#3]

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

[#4]

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.

[#5]

Perfect - thanks!