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
RichardAtkAsked:
Who is Participating?
 
Rob HensonFinance 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
 
gowflowCommented:
Sorry but you uploaded
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
 
Rob HensonFinance 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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Rob HensonFinance 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
 
RichardAtkAuthor 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
 
RichardAtkAuthor Commented:
Perfect many thanks
0
 
Roy CoxGroup 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.

All Courses

From novice to tech pro — start learning today.