 # 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. Thanks.

4 Replies

# Re: Formula Help - Create formula based on last column entry

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

# Re: Formula Help - Create formula based on last column entry

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

# Re: Formula Help - Create formula based on last column entry

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.

# Re: Formula Help - Create formula based on last column entry

Perfect - thanks!