Cumulative count according to value in another cell

[ejsdon]

Ok, I am struggling with this one a little bit. If you open the sheet I have attached this will be much easier to explain.

I have a list of tasks that will be done - they have a planned date, a forecast date and an actual completed date. In my Actual Completion Column (K) I want the data to display as follows:

- I want the percentage value to be counted from column G
- I only want this percentage to count once the Actual Date column F has been populated
- I want the actual completion to be cumulative from all values that have been added in so far
- the actual date in column F will trigger the addition of the pecentage from column G into column K - but only from the correct date in column I

Please ignore the planned completion number only going to 90% - I have more data to go in.

Thank You in advance for your help!

[Glenn Kennedy]

This SHOULD work

=IF(F4="","",G4)

BUT it depends on what you are seeing in column F. If you have a pile of irritating 0 Jan 00, amend that formula to read:

=IF('Yourpathway'!F4="","",='Yourpathway'!F4)

[ejsdon]

Hi Glenn, see my private message reply.

[Glenn Kennedy]

Done. can you see all the 0 Jan/00 values?

[Glenn Kennedy]

Another way to get rid of them is =iferror(1/(1/your_reference),"")

[ejsdon]

Yes, getting an error screen when I try to input your formula.

I am happy to keep the 0-Jan-00 values - it just means that another date hasn't been inputted from a different tab.

[AliGW]

Hi Glenn, see my private message reply.

It is contrary to the spirit of these forums to take discussions to private messages. All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members. Please keep the entire discussion here in the thread. Thanks.

[Glenn Kennedy]

God, but they would annoy me...

=IF(F4=0,"",G4)

will work

[Glenn Kennedy]

AliGW there was a valid reason.

[ejsdon]

Ok Glenn - thank you that is working - though not cumulative with previous value. And theer are blanks between the change in values - I would like these to be the same as the highest value to date.

Sorry for the bother.

Hi AliGW - I was asking Glenn by private message to remove a pathway that identified my work scope - I understand the ethos of the forum and will avoid private messaging wherever possible.

[AliGW]

Understood - thanks for the clarification. May I suggest that you do it yourself next time before posting your sample sheet, which anyone can download?

[Glenn Kennedy]

Missed that bit. One way:

=IF(F4=0,"",G4+IFERROR(LOOKUP(1E+100,$K$3:K3),0))

[ejsdon]

Ok Glenn that is working - Thank You. One more thing - between the updated values in column K I have blanks - how do I make these equal the last value - i.e. K5 = 0.022222222 - K6 to K* are blank - I'd like them to display the latest vale, i.e. 0.022222222 - and for taht to happen between each change.

[Glenn Kennedy]

K3
=IF(F4=0,0,G4)

K4 and down:
=IF(F5=0,K4,G5+K4)

[ejsdon]

Thank You Glenn, you've been a great help.

[Glenn Kennedy]

You're welcome. We got here in the end!!