Help with OFFSET formula

[ofirio]

Hi Everyone,

Hoping to get some help with a formula.

I have a calendar spreadsheet each day is in a column and different names on the rows. I need to calculate the total of the last 7 values entered in the columns for each row. Some rows start on the first column and some do not.

Here is the formula:

=IF((COUNT(E8:AP8)=0),"",(IF((COUNT(E8:AP8)>=7),SUM(OFFSET(E8,0,COUNT(E8:AP8)-7,1,31)),SUM(E8:AP8))))

I know this formula might be a bit convoluted but it's mostly working. The only problem is that rows that the first value does not start on E8 (the first column) the formula is simply adding up all the values in that row with no offset.

Sample spreadsheet attached.

Note: I am using this formula in Google Sheets.

[TMS]

Maybe this:

Please Login or Register  to view this content.

copied down

[TMS]

And, with your constraints:

Please Login or Register  to view this content.

[Limor_OP]

Hi,
the easy way is to add "0" in the empty cells, so the count function will count these cells.

[Glenn Kennedy]

One way:

=SUM(INDEX(8:8,N(IF(1,LARGE(INDEX(($E8:$AH8<>"")*(COLUMN($E8:$AH8)),0),{1,2,3,4,5,6,7})))))

copied down

[Limor_OP]

Maybe this:

Please Login or Register  to view this content.

copied down

Hi TMS, could you please explain the formula? when I evaluate it - it goes down to : sum($R$10:$X$10), but when I press F9 for each segment in the formula , before the ":" and after - I get the actual numbers in each of these cells:
=SUM(7:6)
so how does the formula work?
Thanks.

[ofirio]

Hi Guys,

Thanks so much for the help, I was able to get this working with TMS's formula.

I always think that I'm pretty good with Excel and then I come here and see what real pros look like.

Truly amazing the formulas that you guys come up with off the cuff!

Again, thanks very much for your help!

[TMS]

@Belinda:

Each Index/Match, if evaluated independently, will return the value in the cell. However, combined with the colon ( : ), they will return an array of cells. The SUM then totals the values in the array. In Excel 2019, If you remove the SUM, the array will SPILL into the adjacent cells. So, for row 8, you get: 6 4 5 7 4 6 3

[TMS]

@ofirio: You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[hrlngrv]

This is marked solved, but if this is intended for Google Sheets,

C8:

Please Login or Register  to view this content.

[ofirio]

Hey hrlngrv,

Thanks for your formula!

TMS's solution seems to be working ok for me on Google Sheets, hopefully no issues with it in the future.

[hrlngrv]

TMS's formula is more precise. It uses 2 MATCH calls as arguments to 2 separate INDEX calls to specify the sum range exactly. Mine uses just 1 MATCH and 1 INDEX call to find the 7th from rightmost number then sums to the rightmost extent of the range. Summing over extraneous cells which don't contain numbers does nothing to affect the results.

That said, the 9999+E99 term in TMS's formula means the number 9999 plus the value of cell E99. I suspect he meant 9999E+99 which would be the number with 9999 followed by 99 zeros. 1E308 is a larger number value Excel can handle and requires less typing. Also, in my limited experience with Google Sheets, MATCH(HUGE_value,range) isn't as reliable as in Excel, which is why I used arrayformula(...).

[TMS]

I suspect he meant 9999E+99

I did indeed mean that. By a happy coincidence, the version I actually used was a) large enough and b) didn't get messed up by the contents of the cell E99.

[Limor_OP]

@Belinda:

Each Index/Match, if evaluated independently, will return the value in the cell. However, combined with the colon ( : ), they will return an array of cells......

Wow I didnt know that....Thanks for sharing

[TMS]

OK, just to clarify:

The formula that I offered was this:

Please Login or Register  to view this content.

which works ... but only if cell E99 is blank or has a numeric value. If it has an alphabetic/special character in it, it will fail, as will the formulae below it if cells E100, E101, E102, etc., have a non numeric value in them.

That was not what I intended. What I should have typed was this:

Please Login or Register  to view this content.

which is better but not perfect! In fact, Excel will adjust that to:

Please Login or Register  to view this content.

To quote:

Maximum/Minimum Limitations
All computers have a maximum and a minimum number that can be handled. Because the number of bits of memory in which the number is stored is finite, it follows that the maximum or minimum number that can be stored is also finite. For Excel, the maximum number that can be stored is 1.79769313486232E+308 and the minimum positive number that can be stored is 2.2250738585072E-308.

See: https://docs.microsoft.com/en-us/off...ccurate-result

What I'd normally use is this:

Please Login or Register  to view this content.

but, for whatever reason, I didn't look it up and/or check it out.

You could actually put a lot more 9s after the decimal point but, to be fair, it's a bit tedious and unnecessary.

Please Login or Register  to view this content.

Anyway, I've probably laboured the point enough. Thanks to hrlngrv for highlighting the error before it manifested itself in real life. And, I've got to be honest, I didn't even notice the line that said it was for use in Google Sheets (or I probably wouldn't have touched it)

[ofirio]

Thanks for the update!

Google Sheets also changed it to 9.999E+102 after I typed it, everything appears to be working correctly.

With the original formula I did have the issue with it breaking when there was not a number value in E99 but I changed E99 to the first column in that row's calendar, this cell is either blank or a number so it was working.

Thanks again for your help everyone, much appreciated!

[TMS]

You're welcome