how make a self updating sales target using IF function

[newbizguy]

Ok... I thought I had it and discovered I'm just as close as I was when I started.. I'm having a really hard time explaining this... Please referencee the equation below:

=IF(F5="","",IF(F6>=0,K3,(IF(C6>0,C6,0)+F6)))

Capture.JPG

The above mentioned equation is currently inserted in the highlighted cell (K4) in the picture above. Essentially, the only thing i didn't do was subtract what the "if" statement solves from k3 (hope i'm explaining that right). cell K4 should show $4800. Ultimately if the previous day's "$ to go" is less than zero, than I would subtract that from the sum of all previous positive days and only use that number if its total is less than zero. Therefore, when i get to Sunday's target, if Saturday's "$ to go" is less than zero, than we would subtract that number from all previous positive days and than use it only if that number is less than zero. than finally subtract that number from Sunday's budget. (Ok... I'm lost already and I'm typing this). I really hope someone understands what I'm trying to say. PLEASE HELP!

[Cutter]

The fact that you haven't received any help with this is likely because you have chosen to provide an image instead of an Excel file that people can work with. Try uploading your file (with any personal/sensitive data removed).

[darknation144]

Which rows are calculated and which are fixed?

Why should the yellow cell be $4800 if you are trying to make up for the day before you lost $200 so it should be added to 4000 giving $4200 and not $4800. I will go on my assumption and I propose the option below:

Sales.xls

[newbizguy]

I sent the wrong example. Sorry for that. Atthached is the actual sheet I’m working with. In cell K4, the formula should work to equal F3 being the net profit from the previous 2 days was a gain of $800. Instead, it still subtracted the sum of both C6 & F6. That's why I'm attempting to create a formula based on my absolutely convoluted explanation in the initial post.

[newbizguy]

ok.. i figured out the forumla to make the one cell work the way I want. IS there an easier way to do them for the rest of the days instead of adding an additional IF function to each cell?

correct forumla for K4: =IF(F5="","",IF(F6>=0,K3,(K3-IF((IF(C6>=0,C6,0)+F6)>=0,0,(IF(C6>=0,C6,0)+F6)))))

please see the updated sheet.

[darknation144]

Basically you shouldnt use merge and centre like you have you should have just made the cells larger. Then you can just drag your formula across and it will work.