# Excel fifo formula

Hi

I have a fifo type formula on the attached spread sheet which is not working and need some help with the two sheets should match but don't due to the formula I think being incorrect.   The first sheet should basically look at the second and fulfil column C so if there is 100 on the 2nd sheet and 5 lines of the same item on sheet 1 it should match the qty and then match the remaining onto the next line until it has ran out - if there is more on sheet 2 that's fine.  The problem is negative values and some rogue codes all highlighted on the worksheet. Many thanks I'm stuck
C--Users-richard.atkinson-Desktop-eeexam
###### Who is Participating?

Finance AnalystCommented:
Slight change to the formula for D3 copied down:

=MAX(MIN(SUMIF(Stock!B:B,A3,Stock!C:C)-SUMIFS(C\$2:C2,A\$2:A2,A3,C\$2:C2,">"&0),C3),0)

In the second half of the formula, it uses the SUMIFS to only include column C where greater than zero.
0

Commented:
C--Users-richard.zip

and there is no xls or xlsx files in there bunch of other files and folders
pls post 1 file and try to get the name small so it fits in here.

gowflow
0

Finance AnalystCommented:
What do the numbers in column C represent?

Using Bob as an example, entries in C5 and C6 are -1, the entry in D8 is then 2 because the sum of previous Bob entries (-1, -1, 25) equals 23 and so stock of 25 less 23 leaves 2.

Thanks
Rob H
0

Finance AnalystCommented:
gowflow, I was able to open the file. Clicking on the link gave the usual Open or Save window, I selected Open and then had to browse to Excel and it opened fine.

Thanks
Rob H
0

Author Commented:
Hi

It needs to ignore column C less than 0 and only use the formula  when GT 0 thanks comments on how it should work in G
0

Author Commented:
Perfect many thanks
0

Group Finance ManagerCommented:
Gowflow  -Change the extension from .zip to .xlsx and the file opens in Excel.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.