What is the right IF function to use to sum the last above cell has value and ignore zero?

Originally Posted by Glenn Kennedy

Hi Mohamed. I think that this is what you need. Paste this into C2 and copy down.

Formula:

Please Login or Register  to view this content.

I pasted it but it results nothing, does it miss any ; or , or ( )

Some people can't access dropbox.

You can attach the file here

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Originally Posted by Jonmo1

Some people can't access dropbox.

You can attach the file here

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Attachment added to 1st post.
Attachment 332017

Try this in C3 filled down

=IF(B3=0,0,B3-LOOKUP(2,(1/B$2:B2),B$2:B2))

Originally Posted by Jonmo1

Try this in C3 filled down

=IF(B3=0,0,B3-LOOKUP(2,(1/B$2:B2),B$2:B2))

But the problem is that I do not exactly the cell that will have a value = 0.
I mean there is 366 in a year. and each day has a value and this value could be a zero in case the agent was off or could be equal to a value like 20 or 34 ...etc in case there is a cumulative calls.
So as you can see I need to calculate the net calls for today and the last day that has a value.
Ignore the zero and only add the last value before the zero.
Thanks

Did you try it?

Originally Posted by Jonmo1

Did you try it?

It gives me an error that it is not correct
Untitled.png
\1

You probably need to change the commas to semicolons (, to ; )

=IF(B3=0;0;B3-LOOKUP(2;(1/B$2:B2);B$2:B2))

Originally Posted by Jonmo1

You probably need to change the commas to semicolons (, to ; )

=IF(B3=0;0;B3-LOOKUP(2;(1/B$2:B2);B$2:B2))

Yes this is working well.
Thanks a lot, but please I will test it more with full data and feed you back.
Thanks

Glad to help

But looking back, I think Glenn's formula is better from Post #2. (you need to change the , to ; in that formula as well)

Originally Posted by Jonmo1

Glad to help

But looking back, I think Glenn's formula is better from Post #2. (you need to change the , to ; in that formula as well)

I know this may sound silly but because i am still learning and trying to understand may you please explain the meaning of this formula?
I mean yours and Glenn.
Thanks

Originally Posted by Glenn Kennedy

Did you try my formula and did it work OK, giving you your expected answer?

It did for me, and so much simpler than mine

I'm not even going to bother to explain mine given there is a far simpler solution already provided.

I am afraid that it is not the way I looking for and I am sorry I was not able to explain it the right way from the beginning.
Here is a file to explain it the right way and I do hope that I was able to explain it correctly so please check this file and tell me the right formula.
Given that I do not know when will be the day off for the agent so it could be one day or two or three days but all will be continuously meaning it will be Friday and Saturday or Friday and Saturday and Sunday ...etc
NetCallsFormula.xls

Copy Glenn's formula directly from Post #2.
Paste it into C2 and fill down.

Originally Posted by Glenn Kennedy

My formula is EXACTLY the same as it was in Post 2. It gives the answer that you want. Can you please explain WHY it is not what you want????

YES, this one in the sheet =IF(B2=0;0;B2-SUM(E$1:E1)) working great.

But only one problem, if I want to add a column title what would be the range? I mean for part E in the equation?

Also if I want to determine some exact range in column E and not the full one, what would the case?

Finally may you explain it as I am truly do not understand it. What I understand as follow:-
If B2=0 ; then place 0 in the cell.
If B2 does not = 0 ; then result of this equation (B2 - SUM(E$1:E1)).
Did I get it correctly?
If I get it correctly, then what is the use of the SUM(E$1:E1) ?

Thanks

Originally Posted by Glenn Kennedy

Add a title in column E and see what happens: nothing. It still works (unless the title is a number, in which case the row 2 doesn't work properly.

Yes it is working and I must did something wrong last time.

Originally Posted by Glenn Kennedy

If B2=0 ; then place 0 in the cell.
If B2 does not = 0 ; then result of this equation (B2 - SUM(E$1:E1)).

YES.

Thanks for clearing this.

Originally Posted by Glenn Kennedy

what is the use of the SUM(E$1:E1) ?

It adds up the number of calls in each individual day (E$1:E1) BEFORE TODAY. You subtract that from the total number of calls INLCUDING TODAY to get the number of today's calls.

You are good with the math lol.

One last thing, let's suppose what we did was a range for the 1st agent which has the range e2 to e367 because e1 has the title (366 rows).
and I want to add another range for another agent that has 366 rows as well from e368 to e732 and this range dedicated to the 2nd agent only while the 1st range was dedicated to 1st agent only, in that case what would be the formula for the 2nd range for the 2nd agent because this time I won't need to calculate from the beginning of the sheet @ e2 but will need to start from e367.

I hope that I cleared it enough

Thanks

gentle reminder please.

I am sorry for pushing or hurry things up but I am quite have few hours before I should apply this model and do need to know how to adjust the formula.
Thanks

This equation :-
=IF(B2=0,0,B2-SUM(C$1:C1))
How to let it start from C367 as mentioned in my above mentioned reply?

Originally Posted by Glenn Kennedy

Try this.

You have a slight problem, then, as the equation needs to have a non-numerical cell to start in. I would normally say that you should use:
=IF(B368=0,0,B2-SUM(C$366:C366)). Is there a possibility to leave a one-row gap between the agents? Is the row of header information repeated again? If so, on which row?

Each agents has a range of 366 cells which is the number of days of a whole year.
And as you can see in the attached document that agents are sorted one after or above each others ...etc
Because I do not know when the agent will have a day off or when the agent start a survey, I made the whole year according to the equation shown in the attached document.
And I will enter the success number manually as generated from the system, but will need to apply a formula to the net success
So please take a look and tell me.
But to answer your question, it is repeating after 366 rows (which represent maximum number of any given years) ... etc and so on
I was thinking about if I may add the cell that contains the name of the agent to the equation, I mean to make it look for the agent name and see if it is the same as the agent name in the above cell or not and if it is the same then formula you applied will apply otherwise if agent name is not the same as the name in the above cell then the formula will start to count from this point and so on.
I do not if my idea is applicable or not in excel but just think about it.
Book1.xlsx

Originally Posted by Glenn Kennedy

Why not simply re organise your data?

Because I will use pivot table later on to view this data in a report format.
This is the real file to let see the big picture.
https://www.dropbox.com/s/9nwv8reh8qlo4lh/Book111.xlsx

Book111_[15-07-2014]_[09-01-31].zip
This is the same file in case you can not access the drop box.
Thanks

Originally Posted by Glenn Kennedy

Try this out. I have had to add a helper column. Please note the deliberate inclusion of an empty row between the headers & the first row of data. How does this work for you?

Does adding an empty row will not hard the pivot table result?
If it will not harm the result, then which formula you think is more suitable?
Thanks

Originally Posted by Glenn Kennedy

Try it out yourself & let me know...

Thanks a lot and I owe you a lot.

ok, really much appreciated.