Forum Discussion
Formula Help - Create formula based on last column entry
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.
4 Replies
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.
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".
Perfect - thanks!
nph20 , A variation using INDEX and MATCH using Riny_van_Eekelen workbook.
