How would I go about determining all potential costs from each scenario to maybe index the invoice and see if there is a match?


New Member
Sep 27, 2019
I was given tens of thousands of bills charged to customers. I am trying to determine if the bills are potentially accurate. The only data I have is the actual bill itself. Here's where things get complicated. There are four scenarios, each with their own nuances:
Scenario 1 Nuances: 1. Base Fee of $10 * the amount of items bought * the number of deliveries (maximum amount of items bought would be 10 and the maximum number of deliveries would be 7) 2. If the amount of items bought exceeds 1, the total bill is discounted by 20% 3. If the boxes are recycled, the total bill is further discounted by 15% 4. If the good is over 15 pounds, an additional charge of $10 will be assessed per each item over 15 pounds (maximum amount of items would be 10) 5. If there is no adequate port for delivery, an additional $50 per hour will be charged per delivery (maximum hours would be 3)
Scenario 2 Nuances: 1. Business that share a port of delivery for the second service will be charged $11 per item 2. If the business does not share a port of delivery, the fee is $13 per item. Each additionally delivery for a business that does not share a port is $15 per delivery, in addition to each item costing $13 (maximum deliveries would be 7 and maximum amount of items would be 8).
You see where I am going with all of this. The next two scenarios are just as lengthy. Sadly, no information besides the total bill is given. We don't know which scenario the bill belongs to, the number of items, etc., just a single column of the invoice. How would I go about determining all potential costs from each scenario to maybe index the invoice and see if there is a match? Any help on this would be amazing.

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)


MrExcel MVP, Moderator
Jun 12, 2014
Office Version
  1. 365
  1. Windows
Cross posted on multiple sites

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply links to ALL sites where you have asked this question.

Watch MrExcel Video

Forum statistics

Latest member