|
I'm performing an analysis of shipping costs at my new job. We received a report on every shipment sent in 2023, but the data was quite messy and I've been trying to clean it up. Some of our orders ship in multiple packages, and each of our 3 carriers reports that differently. FedEx (in blue) reported the full charge on the line for the first package, and a $.01 charge for every subsequent package. UPS was initially just wrong, and reported the full shipping charge for every package (red) in column AM. I fixed that with a silly formula to basically match the FedEx format (orange) in column AL. This worked for the first analysis we did, but there are problems with this hacky solution for other analyses. Our last shipper, GSO, averages the shipping charge over every line/package. I'd like to take the FedEx and UPS reporting, and change it to the GSO reporting. The reference number column (AV) has a unique first 13 digits for every order, and if an order has multiple packages it's noted in the last 2 digits (green). I've been trying to use the reference numbers to somehow define a range of shipping rates that would be averaged into a new column, but I've been running into a wall for how to do this. My last attempt was using =AVERAGEIF to define logic around that but it fell apart with a DIV/0 error, and was very hacky and I didn't like it anyway. If there's a generally accepted solution to this sort of issue I'd be happy to learn it, I've just been struggling with my search terms. Thanks!
|
#
¿
Jan 31, 2024 02:00
|
|
|
|
| # ¿ May 12, 2024 08:42 |
|
|
Wonderful, that was a much more elegant fix than anything I was working through. The helper column clarified things significantly. Now I need to read up more about how to use *IFS functions.
|
|
#
¿
Jan 31, 2024 05:13
|
|