Sep 20 2022 11:04 AM - edited Sep 20 2022 11:07 AM
In our business, we purchase fuel in bulk and it is then "sold" to our equipment at cost. I need to be able to attribute a price to each sale.
I keep feeling like I've almost got it and then it breaks or just doesn't add up. I'd say I'm fairly competent at excel, but I've never used VBA or Macros. I'm not unwilling to learn if it solves this project for me.
A few notes:
I have two tables of data, for Purchases and Sales.
PURCHASES
Date | Purchase Quantity | Purchase Price |
2/25/22 | 22689 | 1.4023 |
2/25/22 | 27278 | 1.3913 |
3/18/22 | 48319 | 1.4446 |
3/30/22 | 43223 | 1.6663 |
4/14/22 | 45898 | 1.4318 |
4/29/22 | 38014 | 1.6534 |
5/08/22 | 39082 | 1.5981 |
5/20/22 | 42052 | 1.4674 |
SALES
Date | Time | Quantity Sold | Equip# |
3/08/22 | 4:26:00 PM | 293 | 263 |
3/09/22 | 11:16:00 AM | 79 | 110 |
3/09/22 | 11:22:00 AM | 110 | 127 |
3/09/22 | 2:41:00 PM | 613 | 276 |
3/09/22 | 4:03:00 PM | 348 | 279 |
3/09/22 | 4:52:00 PM | 68 | 110 |
3/09/22 | 5:11:00 PM | 571 | 260 |
3/09/22 | 8:04:00 PM | 572 | 278 |
3/10/22 | 4:55:00 AM | 405 | 429 |
3/10/22 | 11:42:00 AM | 598 | 263 |
3/10/22 | 11:54:00 AM | 71 | 276 |
3/10/22 | 12:05:00 PM | 138 | 276 |
3/10/22 | 12:44:00 PM | 328 | 276 |
3/10/22 | 12:55:00 PM | 428 | 262 |
3/10/22 | 12:59:00 PM | 155 | 453 |
3/10/22 | 1:02:00 PM | 21 | 606 |
3/10/22 | 2:34:00 PM | 272 | 607 |
3/10/22 | 4:09:00 PM | 319 | 254 |
3/10/22 | 7:37:00 PM | 219 | 261 |
3/10/22 | 7:44:00 PM | 217 | 261 |
3/10/22 | 9:44:00 PM | 72 | 110 |
3/10/22 | 10:29:00 PM | 647 | 276 |
3/10/22 | 10:45:00 PM | 534 | 260 |
3/11/22 | 4:39:00 AM | 547 | 279 |
3/11/22 | 2:09:00 PM | 241 | 609 |
3/11/22 | 3:59:00 PM | 82 | 134 |
3/11/22 | 4:05:00 PM | 284 | 260 |
3/11/22 | 4:06:00 PM | 313 | 608 |
3/11/22 | 4:37:00 PM | 413 | 271 |
3/11/22 | 4:51:00 PM | 265 | 276 |
3/11/22 | 7:25:00 PM | 321 | 607 |
3/11/22 | 7:25:00 PM | 138 | 609 |
3/11/22 | 7:50:00 PM | 386 | 429 |
3/11/22 | 10:26:00 PM | 557 | 261 |
3/12/22 | 7:41:00 AM | 252 | 279 |
3/12/22 | 11:59:00 AM | 73 | 127 |
3/12/22 | 4:12:00 PM | 69 | 110 |
3/12/22 | 4:24:00 PM | 169 | 608 |
3/12/22 | 5:36:00 PM | 594 | 275 |
3/12/22 | 7:27:00 PM | 254 | 607 |
3/13/22 | 3:54:00 AM | 395 | 263 |
3/13/22 | 7:06:00 AM | 177 | 276 |
3/13/22 | 7:40:00 AM | 537 | 279 |
3/13/22 | 10:12:00 AM | 52 | 134 |
3/13/22 | 11:15:00 AM | 665 | 278 |
3/13/22 | 2:33:00 PM | 389 | 607 |
3/13/22 | 3:35:00 PM | 64 | 121 |
3/13/22 | 3:59:00 PM | 318 | 264 |
3/13/22 | 4:01:00 PM | 443 | 276 |
3/13/22 | 4:55:00 PM | 116 | 110 |
3/13/22 | 6:36:00 PM | 262 | 610 |
3/14/22 | 5:20:00 AM | 500 | 612 |
3/14/22 | 5:47:00 AM | 243 | 258 |
3/14/22 | 5:50:00 AM | 378 | 279 |
3/14/22 | 5:51:00 AM | 172 | 258 |
3/14/22 | 9:31:00 AM | 530 | 608 |
3/14/22 | 10:44:00 AM | 266 | 601 |
3/14/22 | 11:52:00 AM | 76 | 134 |
3/14/22 | 1:10:00 PM | 111 | 607 |
3/14/22 | 1:41:00 PM | 309 | 276 |
3/14/22 | 1:51:00 PM | 462 | 275 |
3/14/22 | 4:41:00 PM | 113 | 124 |
3/14/22 | 4:44:00 PM | 51 | 125 |
3/14/22 | 6:38:00 PM | 232 | 273 |
3/14/22 | 8:08:00 PM | 174 | 127 |
3/14/22 | 9:25:00 PM | 130 | 134 |
3/15/22 | 5:29:00 AM | 156 | 612 |
3/15/22 | 7:24:00 AM | 440 | 261 |
3/15/22 | 7:47:00 AM | 135 | 279 |
3/15/22 | 7:55:00 AM | 192 | 279 |
3/15/22 | 10:03:00 AM | 210 | 275 |
3/15/22 | 11:18:00 AM | 597 | 258 |
3/15/22 | 12:10:00 PM | 108 | 110 |
3/15/22 | 1:16:00 PM | 470 | 261 |
3/15/22 | 7:39:00 PM | 156 | 124 |
3/16/22 | 4:47:00 AM | 195 | 612 |
3/16/22 | 6:16:00 AM | 149 | 279 |
3/16/22 | 8:35:00 AM | 35 | 127 |
3/16/22 | 12:59:00 PM | 520 | 607 |
3/16/22 | 1:07:00 PM | 608 | 275 |
3/16/22 | 3:47:00 PM | 546 | 280 |
3/16/22 | 3:49:00 PM | 258 | 117 |
3/16/22 | 3:53:00 PM | 217 | 280 |
3/16/22 | 5:00:00 PM | 180 | 273 |
3/16/22 | 5:16:00 PM | 487 | 264 |
3/16/22 | 6:10:00 PM | 327 | 279 |
3/16/22 | 6:10:00 PM | 71 | 110 |
3/16/22 | 7:08:00 PM | 286 | 610 |
3/16/22 | 7:47:00 PM | 194 | 127 |
3/16/22 | 10:17:00 PM | 503 | 258 |
3/17/22 | 2:53:00 AM | 344 | 276 |
3/17/22 | 3:05:00 AM | 360 | 612 |
3/17/22 | 6:37:00 AM | 139 | 125 |
3/17/22 | 8:43:00 AM | 392 | 608 |
3/17/22 | 8:59:00 AM | 312 | 609 |
3/17/22 | 11:26:00 AM | 516 | 273 |
3/17/22 | 12:48:00 PM | 76 | 110 |
3/17/22 | 2:02:00 PM | 101 | 127 |
3/17/22 | 2:15:00 PM | 272 | 610 |
3/17/22 | 5:14:00 PM | 55 | 117 |
3/17/22 | 9:36:00 PM | 474 | 264 |
3/18/22 | 4:41:00 AM | 391 | 275 |
3/18/22 | 4:45:00 AM | 449 | 279 |
3/18/22 | 6:34:00 AM | 273 | 263 |
3/18/22 | 6:39:00 AM | 291 | 263 |
3/18/22 | 8:48:00 AM | 450 | 273 |
3/18/22 | 10:33:00 AM | 114 | 127 |
3/18/22 | 10:40:00 AM | 175 | 120 |
3/18/22 | 10:52:00 AM | 236 | 610 |
3/18/22 | 2:11:00 PM | 270 | 278 |
3/18/22 | 2:13:00 PM | 334 | 607 |
3/18/22 | 2:15:00 PM | 145 | 278 |
3/18/22 | 5:22:00 PM | 87 | 152 |
3/18/22 | 7:26:00 PM | 51 | 110 |
3/18/22 | 8:13:00 PM | 619 | 262 |
3/18/22 | 9:45:00 PM | 490 | 608 |
3/18/22 | 10:57:00 PM | 177 | 120 |
3/19/22 | 1:42:00 AM | 285 | 264 |
3/19/22 | 5:15:00 AM | 402 | 275 |
3/19/22 | 10:31:00 AM | 203 | 258 |
3/19/22 | 10:37:00 AM | 270 | 258 |
3/19/22 | 11:39:00 AM | 720 | 260 |
3/19/22 | 1:35:00 PM | 183 | 607 |
3/19/22 | 2:06:00 PM | 82 | 127 |
3/19/22 | 3:06:00 PM | 368 | 117 |
3/19/22 | 10:01:00 PM | 727 | 280 |
3/19/22 | 11:55:00 PM | 337 | 264 |
3/20/22 | 10:41:00 AM | 302 | 276 |
3/20/22 | 12:02:00 PM | 303 | 262 |
3/20/22 | 12:05:00 PM | 371 | 255 |
3/20/22 | 12:10:00 PM | 188 | 262 |
3/20/22 | 1:03:00 PM | 645 | 274 |
3/20/22 | 1:14:00 PM | 429 | 279 |
3/20/22 | 1:15:00 PM | 362 | 275 |
3/20/22 | 1:41:00 PM | 247 | 607 |
3/20/22 | 2:50:00 PM | 72 | 110 |
3/20/22 | 4:29:00 PM | 469 | 271 |
3/20/22 | 5:49:00 PM | 520 | 273 |
3/20/22 | 7:22:00 PM | 304 | 264 |
3/21/22 | 2:14:00 AM | 251 | 280 |
3/21/22 | 3:24:00 AM | 30 | 110 |
3/21/22 | 3:39:00 AM | 367 | 254 |
3/21/22 | 4:59:00 AM | 405 | 604 |
3/21/22 | 5:18:00 AM | 43 | 116 |
3/21/22 | 5:25:00 AM | 395 | 610 |
3/21/22 | 5:32:00 AM | 417 | 612 |
3/21/22 | 10:07:00 AM | 107 | 127 |
3/21/22 | 10:32:00 AM | 107 | 124 |
3/21/22 | 12:16:00 PM | 367 | 273 |
3/21/22 | 1:27:00 PM | 465 | 607 |
3/21/22 | 4:28:00 PM | 277 | 125 |
3/21/22 | 6:25:00 PM | 709 | 276 |
3/21/22 | 6:35:00 PM | 457 | 609 |
3/21/22 | 7:49:00 PM | 686 | 278 |
3/22/22 | 1:04:00 AM | 231 | 604 |
3/22/22 | 4:53:00 AM | 536 | 275 |
3/22/22 | 5:43:00 AM | 130 | 110 |
3/22/22 | 10:24:00 AM | 311 | 273 |
3/22/22 | 10:57:00 AM | 51 | 116 |
3/22/22 | 2:51:00 PM | 102 | 138 |
3/22/22 | 3:12:00 PM | 676 | 258 |
3/22/22 | 4:49:00 PM | 326 | 426 |
3/22/22 | 4:53:00 PM | 87 | 426 |
3/22/22 | 6:25:00 PM | 258 | 280 |
3/22/22 | 7:37:00 PM | 327 | 262 |
3/22/22 | 10:08:00 PM | 250 | 609 |
3/23/22 | 4:48:00 AM | 338 | 278 |
3/23/22 | 5:41:00 AM | 421 | 604 |
3/23/22 | 7:14:00 AM | 148 | 124 |
3/23/22 | 1:45:00 PM | 76 | 606 |
3/23/22 | 1:56:00 PM | 235 | 268 |
3/23/22 | 2:18:00 PM | 55 | 115 |
3/23/22 | 3:14:00 PM | 395 | 608 |
3/23/22 | 6:32:00 PM | 499 | 280 |
3/23/22 | 6:37:00 PM | 509 | 276 |
3/23/22 | 8:05:00 PM | 242 | 273 |
3/23/22 | 10:35:00 PM | 157 | 134 |
3/24/22 | 2:09:00 AM | 292 | 609 |
3/24/22 | 2:37:00 AM | 677 | 263 |
3/24/22 | 2:39:00 AM | 216 | 264 |
3/24/22 | 3:13:00 AM | 312 | 258 |
3/24/22 | 5:17:00 AM | 63 | 116 |
3/24/22 | 7:32:00 AM | 406 | 610 |
3/24/22 | 12:14:00 PM | 293 | 273 |
3/24/22 | 2:06:00 PM | 96 | 110 |
3/24/22 | 4:19:00 PM | 386 | 609 |
3/24/22 | 7:16:00 PM | 421 | 275 |
3/24/22 | 8:05:00 PM | 276 | 607 |
3/24/22 | 8:15:00 PM | 703 | 279 |
3/24/22 | 11:29:00 PM | 429 | 258 |
3/24/22 | 11:45:00 PM | 328 | 610 |
3/25/22 | 5:58:00 AM | 213 | 278 |
3/25/22 | 7:05:00 AM | 260 | 263 |
3/25/22 | 8:22:00 AM | 164 | 127 |
3/25/22 | 9:55:00 AM | 403 | 608 |
3/25/22 | 10:33:00 AM | 194 | 610 |
3/25/22 | 10:38:00 AM | 524 | 262 |
3/25/22 | 11:32:00 AM | 158 | 607 |
3/25/22 | 12:44:00 PM | 81 | 110 |
3/25/22 | 1:45:00 PM | 432 | 260 |
3/25/22 | 2:09:00 PM | 331 | 278 |
3/25/22 | 5:25:00 PM | 570 | 279 |
3/25/22 | 7:12:00 PM | 115 | 110 |
3/25/22 | 7:37:00 PM | 609 | 264 |
3/25/22 | 10:48:00 PM | 676 | 280 |
3/26/22 | 1:53:00 AM | 230 | 275 |
3/26/22 | 6:02:00 AM | 344 | 608 |
3/26/22 | 8:54:00 AM | 30 | 110 |
3/26/22 | 8:59:00 AM | 414 | 273 |
3/26/22 | 1:27:00 PM | 210 | 453 |
3/26/22 | 1:27:00 PM | 101 | 127 |
3/26/22 | 1:30:00 PM | 136 | 453 |
3/26/22 | 1:57:00 PM | 138 | 125 |
3/26/22 | 3:16:00 PM | 343 | 607 |
3/26/22 | 4:25:00 PM | 327 | 279 |
3/26/22 | 6:25:00 PM | 616 | 276 |
3/26/22 | 6:56:00 PM | 439 | 280 |
3/26/22 | 8:02:00 PM | 721 | 258 |
3/26/22 | 8:15:00 PM | 360 | 610 |
3/27/22 | 2:20:00 AM | 256 | 453 |
3/27/22 | 6:09:00 AM | 386 | 275 |
3/27/22 | 6:41:00 AM | 370 | 264 |
3/27/22 | 7:28:00 AM | 77 | 110 |
3/27/22 | 7:47:00 AM | 118 | 261 |
3/27/22 | 7:55:00 AM | 149 | 261 |
3/27/22 | 8:58:00 AM | 146 | 610 |
3/27/22 | 9:08:00 AM | 62 | 116 |
3/27/22 | 12:27:00 PM | 131 | 127 |
3/27/22 | 12:54:00 PM | 319 | 263 |
3/27/22 | 1:51:00 PM | 215 | 608 |
3/27/22 | 3:33:00 PM | 342 | 453 |
3/27/22 | 3:44:00 PM | 61 | 116 |
3/27/22 | 4:45:00 PM | 434 | 276 |
3/27/22 | 5:25:00 PM | 642 | 260 |
3/27/22 | 6:07:00 PM | 260 | 275 |
3/27/22 | 6:21:00 PM | 661 | 280 |
3/27/22 | 7:17:00 PM | 433 | 255 |
3/27/22 | 7:19:00 PM | 477 | 273 |
3/27/22 | 7:21:00 PM | 76 | 110 |
3/28/22 | 8:54:00 AM | 328 | 273 |
3/28/22 | 9:43:00 AM | 413 | 607 |
3/28/22 | 9:45:00 AM | 209 | 120 |
3/28/22 | 12:38:00 PM | 68 | 116 |
3/28/22 | 2:27:00 PM | 561 | 261 |
3/28/22 | 5:03:00 PM | 147 | 127 |
3/28/22 | 8:24:00 PM | 519 | 610 |
3/28/22 | 8:56:00 PM | 532 | 276 |
3/29/22 | 4:32:00 AM | 529 | 280 |
3/29/22 | 5:04:00 AM | 238 | 604 |
3/29/22 | 9:06:00 AM | 75 | 116 |
3/29/22 | 9:10:00 AM | 334 | 279 |
3/29/22 | 10:04:00 AM | 153 | 300 |
3/29/22 | 10:42:00 AM | 443 | 271 |
3/29/22 | 12:22:00 PM | 65 | 110 |
3/29/22 | 12:41:00 PM | 268 | 610 |
3/29/22 | 1:38:00 PM | 238 | 607 |
3/29/22 | 1:44:00 PM | 457 | 609 |
3/29/22 | 2:50:00 PM | 650 | 260 |
3/29/22 | 5:29:00 PM | 137 | 127 |
3/30/22 | 3:02:00 AM | 111 | 120 |
3/30/22 | 5:33:00 AM | 342 | 604 |
3/30/22 | 8:55:00 AM | 124 | 134 |
3/30/22 | 12:30:00 PM | 135 | 607 |
3/30/22 | 3:34:00 PM | 99 | 104 |
3/30/22 | 6:31:00 PM | 553 | 279 |
3/30/22 | 7:42:00 PM | 317 | 276 |
3/30/22 | 10:31:00 PM | 481 | 261 |
3/31/22 | 2:44:00 AM | 451 | 275 |
3/31/22 | 3:42:00 AM | 340 | 264 |
3/31/22 | 3:47:00 AM | 511 | 273 |
3/31/22 | 3:54:00 AM | 96 | 110 |
3/31/22 | 5:00:00 AM | 250 | 268 |
3/31/22 | 5:03:00 AM | 626 | 280 |
3/31/22 | 5:08:00 AM | 203 | 604 |
3/31/22 | 5:32:00 AM | 87 | 127 |
3/31/22 | 6:17:00 AM | 94 | 134 |
3/31/22 | 7:54:00 AM | 304 | 610 |
3/31/22 | 10:29:00 AM | 325 | 607 |
3/31/22 | 12:01:00 PM | 546 | 453 |
3/31/22 | 12:10:00 PM | 35 | 134 |
3/31/22 | 1:47:00 PM | 387 | 276 |
3/31/22 | 2:01:00 PM | 122 | 127 |
3/31/22 | 2:44:00 PM | 172 | 610 |
3/31/22 | 4:06:00 PM | 76 | 104 |
3/31/22 | 5:37:00 PM | 352 | 268 |
3/31/22 | 5:45:00 PM | 454 | 278 |
3/31/22 | 6:05:00 PM | 61 | 110 |
3/31/22 | 6:10:00 PM | 87 | 134 |
3/31/22 | 6:37:00 PM | 308 | 273 |
3/31/22 | 9:24:00 PM | 109 | 279 |
4/01/22 | 12:24:00 AM | 297 | 264 |
4/01/22 | 3:58:00 AM | 423 | 260 |
4/01/22 | 5:15:00 AM | 465 | 261 |
4/01/22 | 5:15:00 AM | 212 | 604 |
4/01/22 | 5:17:00 AM | 93 | 604 |
4/01/22 | 7:48:00 AM | 80 | 104 |
4/01/22 | 9:06:00 AM | 629 | 275 |
4/01/22 | 9:50:00 AM | 117 | 127 |
4/01/22 | 9:52:00 AM | 192 | 610 |
4/01/22 | 10:09:00 AM | 116 | 116 |
4/01/22 | 12:24:00 PM | 232 | 453 |
4/01/22 | 12:47:00 PM | 44 | 110 |
4/01/22 | 1:15:00 PM | 435 | 279 |
4/01/22 | 1:26:00 PM | 69 | 134 |
4/01/22 | 1:34:00 PM | 56 | 74 |
4/01/22 | 3:12:00 PM | 320 | 607 |
4/01/22 | 4:33:00 PM | 241 | 273 |
4/01/22 | 7:54:00 PM | 586 | 278 |
4/02/22 | 6:05:00 AM | 128 | 264 |
4/02/22 | 7:23:00 AM | 84 | 110 |
4/02/22 | 7:51:00 AM | 349 | 609 |
Sep 20 2022 01:09 PM
Hello! You could use a function without VBA. XLOOKUP allows you to search for the next largest match. For this, I need an auxiliary column in which the purchased quantities are listed cumulatively. Have a look at the solution in the attached sheet to see if it meets your requirements.
Sep 20 2022 01:33 PM
Sep 20 2022 01:55 PM
What should the price be when the price changes. A mixed price?
3/16/2022 | 12:59:00 PM | 520 | 607 | 1.4023 |
3/16/2022 | 1:07:00 PM | 608 | 275 | 1.3936 |
3/16/2022 | 3:47:00 PM | 546 | 280 | 1.3913 |
Sep 20 2022 01:56 PM
Sep 20 2022 02:44 PM
SolutionNow the formula with a mixed price.
Sep 20 2022 04:31 PM
This is a technically far more complicated solution using advanced features of Excel 365.
salesPrice
= LET(
cumPurchases, SCAN(0,purchaseQuantity, Sumλ),
cumSales, SCAN(0, quantitySold, Sumλ),
MAP(cumSales-quantitySold, cumSales,
LAMBDA(s₀,s₁,
SUM(
MAP(purchasePrice, cumPurchases-purchaseQuantity, cumPurchases,
LAMBDA(pr,v₀,v₁,
MAX(MIN(v₁,s₁)- MAX(v₀,s₀),0)* pr
)
)
) / (s₁-s₀)
)
)
);
The formula accumulates both sales and purchase volumes. Then it calculates the overlaps and the price of each tranche. The unit price is obtained by dividing by the sales quantity.
As the sales quantities are small relative to the purchase quantities, very few sales actually require an interpolated price calculation.
Sep 21 2022 09:12 AM
Sep 21 2022 12:08 PM
That is a surprise to me. It would not have been any surprise had you not had access to Excel 365, most users do not realise just how profound the changes since 2018 have been. My belief is that even with a semi-annual license you should have had access to the Lambda helper functions for a while now. Do you control the update cycle, or do you need to go to corporate IT?
@Sergei Baklan Can you advise?
Just in case you can gain access to SCAN and MAP, I have tried to repackage the solution as a Lambda function to facilitate reuse, hopefully for you, but otherwise for anyone else with FIFO based challenges.
Sorksheet formula
= CostFIFOλ(quantitySold, purchaseQuantity, purchasePrice)
CostFIFOλ
= LAMBDA(outputQuantity,inputQuantity,inputCost,
LET(
Addλ, LAMBDA(x, y, x + y),
cumInput, SCAN(0, inputQuantity, Addλ),
cumOutput, SCAN(0, outputQuantity, Addλ),
MAP(cumOutput-outputQuantity, cumOutput,
LAMBDA(u₀,u₁,
SUM(
MAP(cumInput-inputQuantity, cumInput, inputCost,
LAMBDA(v₀,v₁,w,
MAX(MIN(v₁,u₁) - MAX(v₀,u₀), 0) * w
)
)
) / (u₁-u₀)
)
)
)
);
Sep 21 2022 01:06 PM
Sep 21 2022 01:40 PM
Sep 20 2022 02:44 PM
Solution