-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Using date to pull data
- Thread starter FuelGuy
- Start date
Getting a #DIV/0!
How to I get a copy of the spreadsheet to you?
Upvote
0
Excel Facts
Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 4,568
- Office Version
- 365
- Platform
- Windows
- Web
If you 1) have no blank cells) 2) use the formula I have above EXACTLY you should not get any #DIV/0 errors.
Copy the entire mini worksheet I sent into new worksheet in your workbook (or the same sheet if that works, its better).
If you don't get the errors then move just change the cell references to where your data is.
If you have drop box or other file share services you can post your workbook there. Sanitize your data as needed.
Upvote
0
Upvote
0
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 4,568
- Office Version
- 365
- Platform
- Windows
- Web
What is the purpose of the @ sign in your formula?
Without that, I get this:
Without that, I get this:
| data (4).xlsx | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Timestamp (CDT) | Bulk Tank | Day | ||||
| 2 | 2023-03-01 15:07 | 7983.964 | 2023-03-01 00:00 | #DIV/0! | 615.1807 | ||
| 3 | 2023-03-01 15:22 | 7965.939 | 2023-03-02 00:00 | #DIV/0! | 1072.062 | ||
| 4 | 2023-03-01 15:37 | 7900.111 | 2023-03-03 00:00 | #DIV/0! | 1119.082 | ||
| 5 | 2023-03-01 15:52 | 7809.205 | 2023-03-04 00:00 | #DIV/0! | 61.12598 | ||
| 6 | 2023-03-01 16:07 | 7744.944 | 2023-03-05 00:00 | #DIV/0! | -3.91846 | ||
| 7 | 2023-03-01 16:22 | 7697.140 | 2023-03-06 00:00 | #DIV/0! | 1162.184 | ||
| 8 | 2023-03-01 16:37 | 7697.140 | 2023-03-07 00:00 | #DIV/0! | 1133.971 | ||
| 9 | 2023-03-01 16:52 | 7588.210 | 2023-03-08 00:00 | #DIV/0! | -6159.65 | ||
| 10 | 2023-03-01 17:07 | 7551.378 | 2023-03-09 00:00 | #DIV/0! | 1401.203 | ||
| 11 | 2023-03-01 17:22 | 7531.003 | 2023-03-10 00:00 | #DIV/0! | 1118.297 | ||
data (4) | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2 | D2 | =INDEX(B2:B8955,MATCH((1/LARGE(IFERROR(1/((--($C2=INT(A2:A8955)))*(A2:A8955)),0),1)),A2:A8955,0),1)-INDEX(B2:B8955,MATCH((1/LARGE(IFERROR(1/((--($C2+1=INT(A2:A8955)))*(A2:A8955)),0),1)),A2:A8955,0),1) |
| E2:E11 | E2 | =INDEX($B$2:$B$8955,MATCH((1/LARGE(IFERROR(1/((--($C2=INT($A$2:$A$8955)))*($A$2:$A$8955)),0),1)),$A$2:$A$8955,0),1)-INDEX($B$2:$B$8955,MATCH((1/LARGE(IFERROR(1/((--($C2+1=INT($A$2:$A$8955)))*($A$2:$A$8955)),0),1)),$A$2:$A$8955,0),1) |
| C3:C11 | C3 | =C2+1 |
| D3:D11 | D3 | =INDEX(B3:B8956,MATCH((1/LARGE(IFERROR(1/((--($C3=INT(A3:A8956)))*(A3:A8956)),0),1)),A3:A8956,0),1)-INDEX(B3:B856,MATCH((1/LARGE(IFERROR(1/((--($C3+1=INT(A3:A8956)))*(A3:A8956)),0),1)),A3:A8956,0),1) |
Upvote
0
Solution
I wasn't using column E. However, between your formula and Fluff's Control Shift Enter it works now. Did notice that the daily totals are off by 5 - 10 gallons but that's good enough for my purposes. Thanks to everybody!
Upvote
0
