Help w/ IF THEN formula

[Anna]

Can anyone help me with this problem???

Cell A B C D
10 10
11 11
12 11
13 12
14 11 R
15 11
16 11
17 12
18 12
19 11 R
20 11

Column A is where I input data. Column C is what I'm
having trouble with. I want the formula in column C to be
the sum of the current cell on the same row in column A
and the previous 7 cells in column A, so far I have
this, "=IF((C11="r"),0,SUM(B4:B11))" and "=IF
((C12="r"),0,SUM(B5:B12))", so on and so forth. So right
now, if there is an "R" in column B, then the value
returned in column C would return 0, otherwise it would
return the sum of the current cell on the same row in
column A and the previous 7 cells in column A. But I also
want the formula in column C to include a "restart" after
the R returns the 0. For example, when the R in cell B14
is entered, the returned value for column C is 0, then the
formula in C15 should no longer calculate the sum of the
current cell on the same row in column A and the previous
7 cells in column A. It should start over at 0. It should
only go as far back as the last R entered. It should be
the sum of A14 and A15. Cell C16 should be the sum of
A14, A15 and A16, so on and so forth. I want to be able
to have a formula in column C that will go back 7 days if
no R was entered and if R is entered then it will only go
back as far as the last R entered, sort of like starting
over.

If anyone could give me any input I would really
appreciate it. Thanks so much. Please email
(e-mail address removed) if need additional information.
Thanks in advance!!!

 

[duane]

ok i give up...do the r's go in column b or c? which column is to b
added

 

[duane]

assuming that col b has the data (10,11,11,12, etc) starting in row 4
and col c has the occasional R's.....


column D startiing in row 4

=IF(C4="R",ROW(C4),0)

column E starting in row 8 (this is really a mess!)

=IF(C8="R",0,IF(MAX(D17)=0,SUM(B1:B7),SUM(B8:OFFSET(B7,MAX(-6,(OFFSET(D7,MAX(D17)-ROW(C7),0,1,1)-ROW(D8)+1)),0,1,1))))

seems to gave gotten the right result

the logic being to flag the row #'s of the "R"s, so you can figure ou
how many rows back up to sum - the previous max in col D will be th
previous R row

Good Luck!


B C D E
10 this is row 4
11
11
12
11 R 8 0
11 22
11 33
12 45
12 57
11 R 13 0
11 2

 

[Guest]

I have a solution for you - it's not the most elegant, but it works. There
are four different formulas here. The formulas for C2 and D2 should be
copied down as far as you need them.

C1: =A1

D1: =IF(B1="R",1,1) 'this places a '1' in the cell - you don't need the
formula....

C2: =IF(D2>1,SUM(C1,A2),A2)

D2: =IF(B2="R",1,IF((D1+1)>7,1,D1+1))

 

[Guest]

Ooops!

Not exactly what you were looking for - try this instead...

The formulas for D2 and E2 should be copied down as far as you need them.

D1: =A1

E1: =IF(B1="R",1,1) 'this places a '1' in the cell - you don't need the
formula....

D2: =IF(E2>1,SUM(D1,A2),A2)

E2: =IF(B2="R",7,IF((E1+1)>7,1,E1+1))

C1: =IF(B1="R",D1,IF(E1=7,D1,""))
This only puts the sum if there is an "R" or if there are 7 entries without
and "R" amongst them.

 

[Guest]

The formula in C1 should be copied down as far as you need also....

 

[Guest]

Hi Anna,

You can ignore my previous posts - the suggestions didn't work the way you
wanted. My mistake. However, I suggest you use duane's formula with the
following change:

Instead of: OFFSET(B7,MAX(-6......

Use : OFFSET(B7,MAX(-5......

Hi duane,

I tested using all 10's in the data column. Under that circumstance, the
max sum should be 70 but it was getting up to 80 in the 7th row after the "r"
entry. The above change keeps it at a max of 70 in the test.

 

[Ron Rosenfeld]

Can anyone help me with this problem???

Cell A B C D
10 10
11 11
12 11
13 12
14 11 R
15 11
16 11
17 12
18 12
19 11 R
20 11

Column A is where I input data. Column C is what I'm
having trouble with. I want the formula in column C to be
the sum of the current cell on the same row in column A
and the previous 7 cells in column A, so far I have
this, "=IF((C11="r"),0,SUM(B4:B11))" and "=IF
((C12="r"),0,SUM(B5:B12))", so on and so forth. So right
now, if there is an "R" in column B, then the value
returned in column C would return 0, otherwise it would
return the sum of the current cell on the same row in
column A and the previous 7 cells in column A. But I also
want the formula in column C to include a "restart" after
the R returns the 0. For example, when the R in cell B14
is entered, the returned value for column C is 0, then the
formula in C15 should no longer calculate the sum of the
current cell on the same row in column A and the previous
7 cells in column A. It should start over at 0. It should
only go as far back as the last R entered. It should be
the sum of A14 and A15. Cell C16 should be the sum of
A14, A15 and A16, so on and so forth. I want to be able
to have a formula in column C that will go back 7 days if
no R was entered and if R is entered then it will only go
back as far as the last R entered, sort of like starting
over.

If anyone could give me any input I would really
appreciate it. Thanks so much. Please email
(e-mail address removed) if need additional information.
Thanks in advance!!!

If I understand you correctly,

1. In column C you want to show a running total of the values in Column A, but
you want that running total to start at the preceding R.

2. If there is an "R", you want the adjacent cell in Column C to show a zero,
but the cell in the next row of column C should include the previous row in
it's running total.

3. You want no more than seven cells in the total.

If that is the case, then this *array* formula will do that. The formula
assumes your data starts in Row 10 and also that column B in that first row
contains an "R".

You can change the first row by changing the references to row 10 accordingly,
but, as written, that first row must contain the "R".

To enter an *array* formula, after typing or pasting the formula into the
formula bar, hold down <ctrl><shift> while you hit <enter>. Excel will place
braces {...} around the formula. You may then copy/drag the formula down as
far as needed.

=(B10<>"R")*SUM(OFFSET(A10,0,0,MAX(
-7,-1-ROW()+MAX(($B$10:B10="R")*ROW(
INDIRECT("10:"&ROW()))))))


--ron

 

[Guest]

Hi,

I tried your formula and I believe there might be a
misunderstanding of the "R". Once there is an "R" entered
in that row in column C,I no longer need to count the data
in that row plus the previous 7 days. Once the R is
entered, it's sort of a starting over, I would start
counting the data from the row where the R was entered
only. Let's say I entered 10 in column B from row 1 thru
row 8 with no R's entered in column C. Then D8 should
yield me a result of 80 because it's calculation the sum
of rows 1-8. And let's say I entered 10's in rows 11-20
with an R entered in row 12. Row 11 would yield 80 also
because it's calculating rows 4-11, total of 8 rows of 10
each. Row 12 would yield 0 because once R is entered it
yields a 0. But row 13 should yield 20 because it's
counting data in row 13 and row 12. I'm not sure if I am
making sense to you. If not please let me know. I really
appreciate your help. Thanks!

anna

 

[Ron Rosenfeld]

3. You want no more than seven cells in the total.

If that is the case, then this *array* formula will do that. The formula
assumes your data starts in Row 10 and also that column B in that first row
contains an "R".

You can change the first row by changing the references to row 10 accordingly,
but, as written, that first row must contain the "R".

To enter an *array* formula, after typing or pasting the formula into the
formula bar, hold down <ctrl><shift> while you hit <enter>. Excel will place
braces {...} around the formula. You may then copy/drag the formula down as
far as needed.

=(B10<>"R")*SUM(OFFSET(A10,0,0,MAX(
-7,-1-ROW()+MAX(($B$10:B10="R")*ROW(
INDIRECT("10:"&ROW()))))))

I noted in a previous response you want a total of eight cells maximum, not
seven. So merely change the -7 to a -8 in the above array formula:


=(B10<>"R")*SUM(OFFSET(A10,0,0,MAX(
-8,-1-ROW()+MAX(($B$10:B10="R")*ROW(
INDIRECT("10:"&ROW()))))))


--ron

 

[Guest]

Thanks for the clarification (and sorry for misunderstanding!) duane's
formula works as you need it then.

 

[duane]

given that explanation, try this for my column E - this equation in row
8 - this sums up to 8 previous rows incl the one you are on

=IF(C8="R",0,IF(MAX(D17)=0,SUM(B1:B8),SUM(B8:OFFSET(B7,MAX(-6,(OFFSET(D7,MAX(D17)-ROW(C7),0,1,1)-ROW(D8)+1)),0,1,1))))

 

[Anna]

Actually I tried it and it doesn't work the way I expect
it to. I'm not getting the right numbers. Maybe I'm
doing something wrong. I'm a beginner and I'm not
understanding why start at row 4? And add a column E?
How does that change my column D? I also tried your last
suggestion where you said you figure out how to do it in
one column (this is actually what I am leaning towards)
but my numbers came out to be all 20's after row 8. I
think I'm getting confuse by all this information. Can
you please break it down for me and if possible give me
step by step instructions?

Let me just clarify what I was trying to do. Basically
this is sort of like a time tracking worksheet. I will
have 31 days so 31 rows on this sheet. I will have a
column to enter hours worked in each day, a column to
enter the R for the restart, and a column where the
calculation is suppose to come out. I'm looking to track
each 8 day periods where they work over 70 hours,
therefore calculating the current day you're on and the
previous 7 days. It'll be nice if there is any way to make
the cell light up to red if they ever go over the 70 hours
but I'm not going to worry about that now until I can get
the formula to work. The other thing is if an R is
entered in a column, then the previous days are pretty
much kicked out, doesn't count anymore. Once that R is
entered the calculation goes back to 0 and we start over.
The row after the R is enter should only count days back
to and including the R. So the day after the R is entered
only counts 2 days (the current day you're on and the day
where the R was entered).

Please help! Thanks so much!

Anna

 

[duane]

Anna I just emailed you my file - take a look and see if it works

 

[Ron Rosenfeld]

Actually I tried it and it doesn't work the way I expect
it to. I'm not getting the right numbers. Maybe I'm
doing something wrong. I'm a beginner and I'm not
understanding why start at row 4? And add a column E?
How does that change my column D? I also tried your last
suggestion where you said you figure out how to do it in
one column (this is actually what I am leaning towards)
but my numbers came out to be all 20's after row 8. I
think I'm getting confuse by all this information. Can
you please break it down for me and if possible give me
step by step instructions?

Let me just clarify what I was trying to do. Basically
this is sort of like a time tracking worksheet. I will
have 31 days so 31 rows on this sheet. I will have a
column to enter hours worked in each day, a column to
enter the R for the restart, and a column where the
calculation is suppose to come out. I'm looking to track
each 8 day periods where they work over 70 hours,
therefore calculating the current day you're on and the
previous 7 days. It'll be nice if there is any way to make
the cell light up to red if they ever go over the 70 hours
but I'm not going to worry about that now until I can get
the formula to work. The other thing is if an R is
entered in a column, then the previous days are pretty
much kicked out, doesn't count anymore. Once that R is
entered the calculation goes back to 0 and we start over.
The row after the R is enter should only count days back
to and including the R. So the day after the R is entered
only counts 2 days (the current day you're on and the day
where the R was entered).

Please help! Thanks so much!

Anna

Did you try the array formula I posted earlier?

=(B10<>"R")*SUM(OFFSET(A10,0,0,MAX(
-8,-1-ROW()+MAX(($B$10:B10="R")*ROW(
INDIRECT("10:"&ROW()))))))


To enter an array formula, after you copy it into the formula bar, hold down
<ctrl><shift> while hitting <enter>.

You may want to modify this for your needs.


--ron

 

[Guest]

Hi Anna,

I've emailed you a spreadsheet with all three formulas side-by-side so that
you can do a comparison of results. I've also included conditional
formatting so that the cell turns red if the number is larger than 70.

The reason that the more complex formulas start further down the rows is
because they are adding up eight rows. If you put the formula on row 7 then
there are not 8 rows to add up and the formula quits (#REF!). I've gone
ahead and entered Ron and duane's formulas starting from "row 1" so you can
see the results.

Note: because of how I set up the sample, their formulas aren't exactly as
posted, but only the row and column references are changed to match the
sample sheet.

At one point I'd entered 31 rows of 'data' with the number 10 and no
restarts. Both Ron's and my formulas will max out at 80 (day 8 thru 31)
while duane's formula max's out at 90 (day 9 thru 31).

The sample sheet has one row for column headings, a blank row, and then 31
rows for data. Column A = 'Day' (1-31), B = data, c = restart, d= rpw
formula, e = Ron formula, f = pt.1 duane formula, g = pt.2 duane formula.
With that setup, here are the formulas:

rpw's-------

cell D3: =IF(C3="R",0,B3)

cell D4 (copied down to D9): =IF(C4="R",0,IF(C3="R",SUM(B4,B3),SUM(B4,D3)))

cell D10 (copied down to D33):
=IF(C10="R",0,IF(C9="R",SUM(B10,B9),IF(COUNTIF(D39,">0")=7,SUM(B3:B10),SUM(B10,D9))))

Ron's------ (starting at cell E3 and copied thru E33
=(C3<>"R")*SUM(OFFSET(B3,0,0,MAX(-8,-1-ROW()+MAX(($C3:C$10="R")*ROW(INDIRECT("10:"&ROW()))))))

duane's------

cell F3 (copied thru F33): =IF(C3="R",ROW(C3),0)
cell G9 (copied thru G33):
=IF(C9="R",0,IF(MAX(F1:F8)=0,SUM(B1:B9),SUM(B9:OFFSET(B8,MAX(-6,(OFFSET(F8,MAX(F1:F8)-ROW(C8),0,1,1)-ROW(F9)+1)),0,1,1))))

If you have any more questions or need some explanation of how these
formulas work, post back and I'm sure the authors can help. In my email to
you I've included a phone number just in case you still need a step-by-step
walk-through.

Rick...

 

[Anna]

Yes I did try your formula but when I did cntr and shift
it didn't do anything, it just pasted the formula in the
cell. Maybe I was doing something wrong. THanks for the
help. Really appreciate it though!

Anna

-----Original Message-----

 

[Guest]

After typing in the formula (and before hitting the enter key) you hold down
the ctrl and the shift keys then hit the enter key. If it was entered
successfully, when you look at the formula in the toolbar the first and last
characters are the brackets - like this:

{=B10<>"R").......etc. &ROW())))))}

After you've entered the formula in one cell, then you copy it to the rest
of the cells in the column. Just in case you don't know how to copy/drag:
When the cursor is in the selected cell, move your mouse over the lower-right
corner. The mouse cursor will change from a thick white plus sign to a thin
black plus sign (depending on you mouse settings). While it is thin-black,
left-click-hold and 'drag' down to the end of the column/range. The selected
cells should be filled with the formula now.

Did you get the sample spreadsheet I sent you? Was it useful?

Rick...

Yes I did try your formula but when I did cntr and shift
it didn't do anything, it just pasted the formula in the
cell. Maybe I was doing something wrong. THanks for the
help. Really appreciate it though!

Anna

 

[Ron Rosenfeld]

Yes I did try your formula but when I did cntr and shift
it didn't do anything, it just pasted the formula in the
cell. Maybe I was doing something wrong. THanks for the
help. Really appreciate it though!

First paste the formula into the cell.

Then, before you do anything else, depress the <ctrl> key and the <shift> key
simultaneously. While you are holding down these two keys, depress and release
the <enter> key. Then release the other two keys.

If you did it correctly, XL will place braces {...} around the formula.

Then select the cell and drag the formula down as far as necessary.
--ron