If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
"Last 7 Days" running total?
If Max will excuse me commenting on his formula the reason it returns zero is
the same reason my formula returns VALUE, your numbers aren't numbers!! "Bob Newman" wrote: This formula is returning "0". Bob "Max" wrote in message ... One idea ... =SUM(OFFSET(INDIRECT("C"&MATCH(MAX(A:A),A:A,0)),,,-7)) Success? hit the YES below -- Max Singapore --- "Bob Newman" wrote: I have a list containing daily sales figures that is updated each day. I would like to have a formulas that will total sales figures (column C) for the last 7 days (column A). Figures are being added daily so the "last 7 days" will be constantly changing. Suggestions please. . |
#13
|
|||
|
|||
"Last 7 Days" running total?
When I use the sum function it totals the cells okay. Doesn't that mean
they are numbers? Bob "Mike H" wrote in message ... This formula is returning "#VALUE!". Then your numbers aren't numbers, you have text in the range Mike "Bob Newman" wrote: This formula is returning "#VALUE!". Bob "Mike H" wrote in message ... Bob, Your question isn't clear but if you want to sum the last 7 entries in column C try this =SUM(OFFSET(C1,COUNTA(C:C)-7,):OFFSET(C1,COUNTA(C:C),)) or to sum any date in the last 7 days =SUMPRODUCT((A1:A5000=TODAY()-6)*(C1:C5000)) Mike "Bob Newman" wrote: Excel 2007 I have a list containing daily sales figures that is updated each day. I would like to have a formulas that will total sales figures (column C) for the last 7 days (column A). Figures are being added daily so the "last 7 days" will be constantly changing. Suggestions please. Thanks in advance... Bob . . |
#14
|
|||
|
|||
"Last 7 Days" running total?
Again: When I use the sum function it totals the cells okay. Doesn't that
mean they are numbers? Bob "Mike H" wrote in message ... If Max will excuse me commenting on his formula the reason it returns zero is the same reason my formula returns VALUE, your numbers aren't numbers!! "Bob Newman" wrote: This formula is returning "0". Bob "Max" wrote in message ... One idea ... =SUM(OFFSET(INDIRECT("C"&MATCH(MAX(A:A),A:A,0)),,,-7)) Success? hit the YES below -- Max Singapore --- "Bob Newman" wrote: I have a list containing daily sales figures that is updated each day. I would like to have a formulas that will total sales figures (column C) for the last 7 days (column A). Figures are being added daily so the "last 7 days" will be constantly changing. Suggestions please. . |
#15
|
|||
|
|||
"Last 7 Days" running total?
Check your dates in col A. These should be real dates recognized by Excel,
not text dates. You can easily convert all text dates in col A at one go to real dates using Data Text to Cols (with col A selected). In step 3 of the wiz, under "Column data format", check "Date", then select the format of the text dates from the droplist, eg: DMY. Click to Finish. Real dates will respond when you say, format it to another date format via FormatCells. Real dates are numbers, increasing chronologically. Hence the latest date = maximum number in col A. -- Max Singapore ----- "Bob Newman" wrote in message ... Again: When I use the sum function it totals the cells okay. Doesn't that mean they are numbers? |
#16
|
|||
|
|||
"Last 7 Days" running total?
They are formatted for "real" dates.
Bob "Max" wrote in message ... Check your dates in col A. These should be real dates recognized by Excel, not text dates. You can easily convert all text dates in col A at one go to real dates using Data Text to Cols (with col A selected). In step 3 of the wiz, under "Column data format", check "Date", then select the format of the text dates from the droplist, eg: DMY. Click to Finish. Real dates will respond when you say, format it to another date format via FormatCells. Real dates are numbers, increasing chronologically. Hence the latest date = maximum number in col A. -- Max Singapore ----- "Bob Newman" wrote in message ... Again: When I use the sum function it totals the cells okay. Doesn't that mean they are numbers? |
#17
|
|||
|
|||
"Last 7 Days" running total?
Do your dates in col A respond properly when you try formatting it to
another date format via FormatCellsDate (choose another date type) ? This is one easy way to test it. Text dates will NOT respond to any kind of date formatting applied via FormatCellsDate. Recheck, let me know the results here. -- Max Singapore ----- "Bob Newman" wrote in message ... They are formatted for "real" dates. Bob "Max" wrote in message ... Check your dates in col A. These should be real dates recognized by Excel, not text dates. You can easily convert all text dates in col A at one go to real dates using Data Text to Cols (with col A selected). In step 3 of the wiz, under "Column data format", check "Date", then select the format of the text dates from the droplist, eg: DMY. Click to Finish. Real dates will respond when you say, format it to another date format via FormatCells. Real dates are numbers, increasing chronologically. Hence the latest date = maximum number in col A. -- Max Singapore ----- "Bob Newman" wrote in message ... Again: When I use the sum function it totals the cells okay. Doesn't that mean they are numbers? |
#18
|
|||
|
|||
"Last 7 Days" running total?
I surrender. This is more difficult than I anticipated. I'll do it
manually. Bob "Max" wrote in message ... Do your dates in col A respond properly when you try formatting it to another date format via FormatCellsDate (choose another date type) ? This is one easy way to test it. Text dates will NOT respond to any kind of date formatting applied via FormatCellsDate. Recheck, let me know the results here. -- Max Singapore ----- "Bob Newman" wrote in message ... They are formatted for "real" dates. Bob "Max" wrote in message ... Check your dates in col A. These should be real dates recognized by Excel, not text dates. You can easily convert all text dates in col A at one go to real dates using Data Text to Cols (with col A selected). In step 3 of the wiz, under "Column data format", check "Date", then select the format of the text dates from the droplist, eg: DMY. Click to Finish. Real dates will respond when you say, format it to another date format via FormatCells. Real dates are numbers, increasing chronologically. Hence the latest date = maximum number in col A. -- Max Singapore ----- "Bob Newman" wrote in message ... Again: When I use the sum function it totals the cells okay. Doesn't that mean they are numbers? |
#19
|
|||
|
|||
"Last 7 Days" running total?
I repeat my offer to send your file If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Bob Newman" wrote in message ... I surrender. This is more difficult than I anticipated. I'll do it manually. Bob "Max" wrote in message ... Do your dates in col A respond properly when you try formatting it to another date format via FormatCellsDate (choose another date type) ? This is one easy way to test it. Text dates will NOT respond to any kind of date formatting applied via FormatCellsDate. Recheck, let me know the results here. -- Max Singapore ----- "Bob Newman" wrote in message ... They are formatted for "real" dates. Bob "Max" wrote in message ... Check your dates in col A. These should be real dates recognized by Excel, not text dates. You can easily convert all text dates in col A at one go to real dates using Data Text to Cols (with col A selected). In step 3 of the wiz, under "Column data format", check "Date", then select the format of the text dates from the droplist, eg: DMY. Click to Finish. Real dates will respond when you say, format it to another date format via FormatCells. Real dates are numbers, increasing chronologically. Hence the latest date = maximum number in col A. -- Max Singapore ----- "Bob Newman" wrote in message ... Again: When I use the sum function it totals the cells okay. Doesn't that mean they are numbers? |
#20
|
|||
|
|||
"Last 7 Days" running total?
But why? The formula should work ok for YOU as well
Why don't you just copy n paste your actual last 7 days dates data here? -- Max Singapore ----- "Bob Newman" wrote in message ... I surrender. This is more difficult than I anticipated. I'll do it manually. Bob |
|
Thread Tools | |
Display Modes | |
|
|