how to do a progressive add with an IF clause that is interrupted.

**john48** · 01-02-2009, 12:26 PM

I am running a sales spreadsheet that requires sales entered each day. I use a simple formula to run a continuous total starting at Monday and ending Sunday (1st cell for sales input is B4, second D4, third F4 and so on. 1st Cell for accumulated sales is C4, second E4 and third G4 and so on.
I use B4+D4 to caluculate the running total in E4 or example,and to eliminate that total appearing in G4 where the next formula is B4+D4+F4 I precede that with an IF formula IF(E4=0,'', ). This way I only show the current Week to date totals, not the upcoming days in the rest of the week. My problem lies in Holidays. If for instance D4, the Tuesday was a holiday and I enter either "holiday" or just leave it blank, the rest of the weeks formula will not work. I get a Value message. How can I achieve my goal of getting a daily week to date total without having it show in the upcoming days AND have a holiday in there too??? Any suggestions would be appreciated. Thank you John

**JBeaucaire** · 01-02-2009, 01:14 PM

Let's see it.

**shg** · 01-02-2009, 02:00 PM

Welcome to the forum, John.

In order to avoid having to answer the same questions every day, we require that posters compose thread titles that make the forum usefully searchable. Your title doesn't advance that goal.

Please take a few minutes to read the Forum Rules about thread titles, and then edit yours to make it descriptive of your problem.

**john48** · 01-02-2009, 04:46 PM

sorry....where can i find out how to edit the title?

**john48** · 01-02-2009, 04:47 PM

how do I post the spreadsheet....it won't copy onto the posting reply?

**shg** · 01-02-2009, 04:54 PM

sorry....where can i find out how to edit the title?

Follow the link in my last post to the forum rules.

how do I post the spreadsheet....it won't copy onto the posting reply?

Click the button with the paper clip, or scroll down to the Manage Attachments button.

pinmaster · 01-02-2009, 05:24 PM

Hi,

Why not just use 1 column for your running total? It would be simpler and cleaner. Then you could use something like this:

SUMIF(B4:F4,">0",B4:F4)

HTH
Jean-Guy

**john48** · 01-02-2009, 06:44 PM

I have attached the spreadsheet which shows the problem. If you were to enter the value 200 in H4 instead of "holiday" the sheet is ok..but I need to be able to enter holidays when they occur. any help would be really appreciated. tx John

**shg** · 01-02-2009, 07:00 PM

Thanks.

The formulas could be made simple if you used a separate row for each item (Plan, Cumulative Plan, Actual, Cumulative Actual, etc.)

Is that a possibility?

**john48** · 01-02-2009, 07:36 PM

I am unfortunately tied to this design because of another project it is tied to. Is there any way to make it work in the current configoration?

**shg** · 01-02-2009, 07:44 PM

Maybe as attached. Please note that the formulas added to cols C, E, G, ... are array formulas. If you edit them, they need to be confirmed with Ctrl+Shift+Enter, not just Enter.

**john48** · 01-02-2009, 08:05 PM

I think you have the solution....how do I fix the remaining problem with the total in P4? tx for so much help John

**shg** · 01-02-2009, 08:08 PM

In P4 just enter =O4

In P5, =O5

**john48** · 01-02-2009, 08:10 PM

Whoops....I also now have the additional problem below in the percentage payroll column
I19 where it refers to H4 (and all the other cells across)...this is getting quite complicated...is there a solution you can help me with again...so sorry to ask but really need to fix this as it is going to be presented in a couple of days..tx a million John

**john48** · 01-02-2009, 08:14 PM

When I do =o5 in P5 it shows the negative number in O5 as a positive in P5?

**shg** · 01-02-2009, 08:15 PM

In C4 and copy to odd columns right, =IF( ISNUMBER(B4), C18 / (B4*1000), "") (though I don't understand the formula)

**john48** · 01-02-2009, 08:25 PM

Do I need array formulas in lines 19,20 and 21? Can I adapt the same ones?

**JBeaucaire** · 01-02-2009, 08:29 PM

I appreciate the arrays and all, but I'm old school. You have cumulative totals available already in the columns, so simple "checks" to see if anything has been entered into the daily PLANNED/ACTUAL before allowing that day's accumulation totals to appear seems good enough, and easier to maintain.

This version of your sheet does what I think you want without any of extra overhead of arrays and such. Worth a look.

**john48** · 01-02-2009, 11:24 PM

Hi Again....I have made alterations based on your suggestions and populated the spreadsheet so you can see what it is supposed to do. The only problem I would like to solve, but not sure how to do it is in the accumulated totals getting ahead of the actual daily entries as you can see in M4 and M5 and O4 and O5. Is there a way to ensure that the totals in those and the similar preceding ones only populate when values are entered into L4 and N4? Thanks again for all your help. John

**john48** · 01-02-2009, 11:54 PM

Hi JB...thanks for your help...I have attached the version of the spreadsheet you posted and illustrated a couple of further solutions needed please! the holiday has effected the cummulative sales in E3 and subsequently the overall total. It has also effected E20 amd E21 which refer to D4...And I would really like the sales variances not to appear until the actual sales for the day are entered as in N5 and o5 which shouldn't show until N4 is entered.
Thanks for all your help so far...I have so much to learn. If you can help me further I would appreciate it. I also posted another version which is interesting but not quite there yet either. So close and yet so far!! tx a million. John

**JBeaucaire** · 01-03-2009, 01:19 AM

I had already implemented a fix for this for you in row 19, leaving rows 20 and 21 for you to do...a chance to apply what you see already being done.

Anyway, here you go.

**john48** · 01-03-2009, 12:12 PM

Hi JB, thank you very much...I didn't spot the earlier fix, but tx for finishing it off...a couple of questions if you don't mind for my own learning. (I did learn the ISBLANK and ISTEXT IF's which was great!), but in the formula in E4 which goes =IF(ISBLANK(D4),0,IF(ISTEXT(D4),C4,D4+C4)), I can follow the logic, but why do you need the D4+C4 after the C4?
I read it as if D4 is blank then 0, if D4 is text then D4+C4, but if D4 is text,then wouldn't you get an error message and shouldn't it more logically just be IF(ISTEXT(D4),C4 only? regards John Sorry, just trying to learn!

**john48** · 01-03-2009, 01:54 PM

Thanks for all the help...I learned some new things as I mentioned, IFTEXT, IFBLANK, and MAX. Have a question about how P$ works and my previous question already posted.
Also learned more about Arrays...must investigage further there.

Anyway...thanks for all the help again...it was really appreciated. regards John

**JBeaucaire** · 01-03-2009, 06:28 PM

Glad to help!

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

**JBeaucaire** · 01-03-2009, 07:56 PM

Originally Posted by john48

Hi JB...in the formula in E4 which goes
=IF(ISBLANK(D4),0,IF(ISTEXT(D4),C4,D4+C4))

I can follow the logic, but why do you need the D4+C4 after the C4?
I read it as if D4 is blank then 0, if D4 is text then D4+C4, but if D4 is text,then wouldn't you get an error message and shouldn't it more logically just be IF(ISTEXT(D4),C4 only? regards John Sorry, just trying to learn!

If D4 is blank, return zero
If D4 has text in the cell, bring over the grand total from C4
If D4 is not blank and does not have text in it, add D4 and C4 together to create a new grand total

OK?