Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic SUM range
I want to be able to SUM the last 4 values in row. I know the exact location
of the last cell so this would appear to be my starting point and I would work backwards from thee. But the cells preceding it may contain a value or there may be a blank cell. There can only be a maximum of 1 blank cell in the row of values I want to sum, so the blank cell may or may not be in the last 4 cells I want to sum. If it is one of the last 4 cells then I want to go back 5 cells so that I end up with 4 values summed. Example: 14 20 23 38 41 blank 28 16 Since I want to sum the last 4 cells with values, I know I want to sum 38+41+28+16 but I have to define my range for the last 5 cells to get 4 valid cells But if I have... Example: 14 20 23 38 41 28 16 I still want to sum the last 4 values but now the range is only the last 4 cells because there is no blank cell in the range. Am I trying to make this too complex? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic SUM range
Hi Carl
you could do it like that: Where the last cell would be F1 =SUM(INDIRECT(CHAR(65 + 6 - 4 - COUNTBLANK(B1:F1)) & "1:F1")) To change the last column use (64 + column of last cell - 4). Hth Carlo "Carl" wrote: I want to be able to SUM the last 4 values in row. I know the exact location of the last cell so this would appear to be my starting point and I would work backwards from thee. But the cells preceding it may contain a value or there may be a blank cell. There can only be a maximum of 1 blank cell in the row of values I want to sum, so the blank cell may or may not be in the last 4 cells I want to sum. If it is one of the last 4 cells then I want to go back 5 cells so that I end up with 4 values summed. Example: 14 20 23 38 41 blank 28 16 Since I want to sum the last 4 cells with values, I know I want to sum 38+41+28+16 but I have to define my range for the last 5 cells to get 4 valid cells But if I have... Example: 14 20 23 38 41 28 16 I still want to sum the last 4 values but now the range is only the last 4 cells because there is no blank cell in the range. Am I trying to make this too complex? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic SUM range
Sorry, forgot to mention, that i built my formula using data in range
A1:F1. Where F1 would be your last known cell. If there are any problems with adjusting the formula, just tell me what your last cell will be. Cheers Carlo "Carl" wrote: I want to be able to SUM the last 4 values in row. I know the exact location of the last cell so this would appear to be my starting point and I would work backwards from thee. But the cells preceding it may contain a value or there may be a blank cell. There can only be a maximum of 1 blank cell in the row of values I want to sum, so the blank cell may or may not be in the last 4 cells I want to sum. If it is one of the last 4 cells then I want to go back 5 cells so that I end up with 4 values summed. Example: 14 20 23 38 41 blank 28 16 Since I want to sum the last 4 cells with values, I know I want to sum 38+41+28+16 but I have to define my range for the last 5 cells to get 4 valid cells But if I have... Example: 14 20 23 38 41 28 16 I still want to sum the last 4 values but now the range is only the last 4 cells because there is no blank cell in the range. Am I trying to make this too complex? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic SUM range
=SUM(N(OFFSET(A1,0,LARGE(IF(A1:F1<"",COLUMN(A1:F1 )-1),{1,2,3,4}))))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Carl" wrote in message ... I want to be able to SUM the last 4 values in row. I know the exact location of the last cell so this would appear to be my starting point and I would work backwards from thee. But the cells preceding it may contain a value or there may be a blank cell. There can only be a maximum of 1 blank cell in the row of values I want to sum, so the blank cell may or may not be in the last 4 cells I want to sum. If it is one of the last 4 cells then I want to go back 5 cells so that I end up with 4 values summed. Example: 14 20 23 38 41 blank 28 16 Since I want to sum the last 4 cells with values, I know I want to sum 38+41+28+16 but I have to define my range for the last 5 cells to get 4 valid cells But if I have... Example: 14 20 23 38 41 28 16 I still want to sum the last 4 values but now the range is only the last 4 cells because there is no blank cell in the range. Am I trying to make this too complex? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic SUM range
Try this:
=SUM(F1:INDEX(A1:F1,LARGE(COLUMN(A1:F1)*(A1:F1<"" ),4))) ctrl+shift+enter, not just enter "Carl" wrote: I want to be able to SUM the last 4 values in row. I know the exact location of the last cell so this would appear to be my starting point and I would work backwards from thee. But the cells preceding it may contain a value or there may be a blank cell. There can only be a maximum of 1 blank cell in the row of values I want to sum, so the blank cell may or may not be in the last 4 cells I want to sum. If it is one of the last 4 cells then I want to go back 5 cells so that I end up with 4 values summed. Example: 14 20 23 38 41 blank 28 16 Since I want to sum the last 4 cells with values, I know I want to sum 38+41+28+16 but I have to define my range for the last 5 cells to get 4 valid cells But if I have... Example: 14 20 23 38 41 28 16 I still want to sum the last 4 values but now the range is only the last 4 cells because there is no blank cell in the range. Am I trying to make this too complex? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic SUM range
Another way...
None array formula, it doesn't required ctrl+shift+enter, Just ENTER =SUM(F1:INDEX(A1:F1,LARGE(INDEX(COLUMN(A1:F1)*(A1: F1<""),0),4))) "Carl" wrote: I want to be able to SUM the last 4 values in row. I know the exact location of the last cell so this would appear to be my starting point and I would work backwards from thee. But the cells preceding it may contain a value or there may be a blank cell. There can only be a maximum of 1 blank cell in the row of values I want to sum, so the blank cell may or may not be in the last 4 cells I want to sum. If it is one of the last 4 cells then I want to go back 5 cells so that I end up with 4 values summed. Example: 14 20 23 38 41 blank 28 16 Since I want to sum the last 4 cells with values, I know I want to sum 38+41+28+16 but I have to define my range for the last 5 cells to get 4 valid cells But if I have... Example: 14 20 23 38 41 28 16 I still want to sum the last 4 values but now the range is only the last 4 cells because there is no blank cell in the range. Am I trying to make this too complex? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic SUM range
Wow! What a great response from Carlo, Bob and Teethless Mama. I will try
each of the recommendations ya'll offered and let you know what I learned and which one I ended up with. Just a though, I said I knew the exact location of the last cell I would use as my starting point, but I didn't mention that that starting location was subject to change. I know I can use END+right arrow to position the active cell to the last cell in the row, but how do I then translate that cell into the formula? Also, I use Excel 2002, does that have any impact on the formulas offered? Thanks again for all your thoughts and ideas. "Carl" wrote: I want to be able to SUM the last 4 values in row. I know the exact location of the last cell so this would appear to be my starting point and I would work backwards from thee. But the cells preceding it may contain a value or there may be a blank cell. There can only be a maximum of 1 blank cell in the row of values I want to sum, so the blank cell may or may not be in the last 4 cells I want to sum. If it is one of the last 4 cells then I want to go back 5 cells so that I end up with 4 values summed. Example: 14 20 23 38 41 blank 28 16 Since I want to sum the last 4 cells with values, I know I want to sum 38+41+28+16 but I have to define my range for the last 5 cells to get 4 valid cells But if I have... Example: 14 20 23 38 41 28 16 I still want to sum the last 4 values but now the range is only the last 4 cells because there is no blank cell in the range. Am I trying to make this too complex? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic SUM range
OK, I tried all of the suggestions offered and did not get satisfactory
results with any of them. I'm not sure what happened with my earlier replay thanks ya'll for the inputs. I had to use Excel help to find out what a few of the functions did so I learned from that but I still dn't know what the N() function does. I did adjust everyone of the formulas offered to match my actual data and maybe that had some impact on the results. I created a separate spreadsheet with all of the formulas and their respective results as well as a breakdown of the formulas to help me understand how they worked. I would gladly provide this spreadsheet to anyone willing to provide additional help but I don't know if there is a way to attach it to this thread. Again thanks for the positive feedback. "Carl" wrote: I want to be able to SUM the last 4 values in row. I know the exact location of the last cell so this would appear to be my starting point and I would work backwards from thee. But the cells preceding it may contain a value or there may be a blank cell. There can only be a maximum of 1 blank cell in the row of values I want to sum, so the blank cell may or may not be in the last 4 cells I want to sum. If it is one of the last 4 cells then I want to go back 5 cells so that I end up with 4 values summed. Example: 14 20 23 38 41 blank 28 16 Since I want to sum the last 4 cells with values, I know I want to sum 38+41+28+16 but I have to define my range for the last 5 cells to get 4 valid cells But if I have... Example: 14 20 23 38 41 28 16 I still want to sum the last 4 values but now the range is only the last 4 cells because there is no blank cell in the range. Am I trying to make this too complex? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic SUM range
N Just converts a cell to its numeric equivalent, required as OFFSET returns
an array of cell references, N changes it to an array of values (to SUM). Send it to me at bob dot phillips at tiscali dot co dot uk do the obvious with dot at and the spaces, and I will look at it. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Carl" wrote in message ... OK, I tried all of the suggestions offered and did not get satisfactory results with any of them. I'm not sure what happened with my earlier replay thanks ya'll for the inputs. I had to use Excel help to find out what a few of the functions did so I learned from that but I still dn't know what the N() function does. I did adjust everyone of the formulas offered to match my actual data and maybe that had some impact on the results. I created a separate spreadsheet with all of the formulas and their respective results as well as a breakdown of the formulas to help me understand how they worked. I would gladly provide this spreadsheet to anyone willing to provide additional help but I don't know if there is a way to attach it to this thread. Again thanks for the positive feedback. "Carl" wrote: I want to be able to SUM the last 4 values in row. I know the exact location of the last cell so this would appear to be my starting point and I would work backwards from thee. But the cells preceding it may contain a value or there may be a blank cell. There can only be a maximum of 1 blank cell in the row of values I want to sum, so the blank cell may or may not be in the last 4 cells I want to sum. If it is one of the last 4 cells then I want to go back 5 cells so that I end up with 4 values summed. Example: 14 20 23 38 41 blank 28 16 Since I want to sum the last 4 cells with values, I know I want to sum 38+41+28+16 but I have to define my range for the last 5 cells to get 4 valid cells But if I have... Example: 14 20 23 38 41 28 16 I still want to sum the last 4 values but now the range is only the last 4 cells because there is no blank cell in the range. Am I trying to make this too complex? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic range | Excel Worksheet Functions | |||
Help With Dynamic Range | Excel Worksheet Functions | |||
Dynamic range | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic range does not appear in name box | Excel Discussion (Misc queries) |