EdmontonGuy
New Member
- Joined
- Jun 24, 2014
- Messages
- 1
I’m new to Excel and I’m fiddling with a formula that I could use a little help with. I’ll layout in basic terms where I’m at with the workbook.
Issue: Big Joe’s Fruit Vendors has 4 vending trucks that they sell fruit from: Big Truck (A), Medium Truck (B), and two small trucks (C and D). Each truck is required to keep the proper amount of fruit types, so when one type gets low they must order more, and when they have too much of one type of fruit they must sell more of that fruit.
Because of the type of truck, there are truck types that can only sell certain types of fruit, and in other cases, fruit types are interchangeable with truck type.
Big Joes carries the following fruits on the following trucks:
Big Truck: Bananas, Oranges, Avocados, Cherries, Raspberries (A)
Medium Truck: Watermelon, Dragon Fruit, Grapes, Cherries, Raspberries (B)
Small Truck: Bananas, Grapes, Cherries (C)
Small Truck: Raspberries, Grapes, Cherries (D)
Each truck has a target percentage for each fruit type, however there must also be a master list that matches with the overall percentage from all the trucks so that Big Joe doesn’t order too much or two little of each fruit when he restocks his trucks. Each month Big Joe adds money from his profits to buy fruit for each truck.
<tbody>
</tbody>
Assuming ‘Bananas’ are on row 6 and ‘Target’ is in column B, and cell C1 is the dollar amount Big Joe is adding to his monthly inventory:
The formula I’m currently using for the amount to buy or sell is =IF($C$11+$C$1)*B6-C6,0)
If we then look at all the trucks and have a separate calculation for Cherries for each truck, assuming we have a set percentage of the total fruit of each type that we want in total inventory:
<tbody>
</tbody>
The formulas I am currently using, assuming ‘Big Truck (A)’ is on row 7, ‘Total Fruit’ is column c, actual fruit is column d, fruit to add is column e, and the total inventory of all of the different types of fruit is located in cell B26:
Cell D7 - =IFERROR(B7/$B$26,0)
Cell E7 =(D7-C7)*$B$26*-1
The formula I’m seeking is one that would allow Big Joe to set his inventory targets for each truck, allow him to add inventory to each truck, but then would also allow the ‘Total Fruit’ column on the overall fruit inventory sheet to be an updated target based on the inventory targets set for each individual truck.
Thanks for any help.
Issue: Big Joe’s Fruit Vendors has 4 vending trucks that they sell fruit from: Big Truck (A), Medium Truck (B), and two small trucks (C and D). Each truck is required to keep the proper amount of fruit types, so when one type gets low they must order more, and when they have too much of one type of fruit they must sell more of that fruit.
Because of the type of truck, there are truck types that can only sell certain types of fruit, and in other cases, fruit types are interchangeable with truck type.
Big Joes carries the following fruits on the following trucks:
Big Truck: Bananas, Oranges, Avocados, Cherries, Raspberries (A)
Medium Truck: Watermelon, Dragon Fruit, Grapes, Cherries, Raspberries (B)
Small Truck: Bananas, Grapes, Cherries (C)
Small Truck: Raspberries, Grapes, Cherries (D)
Each truck has a target percentage for each fruit type, however there must also be a master list that matches with the overall percentage from all the trucks so that Big Joe doesn’t order too much or two little of each fruit when he restocks his trucks. Each month Big Joe adds money from his profits to buy fruit for each truck.
| Money for new fruit | Cell C1 | |||||
| Big Truck | Target Allocation | Current Fruit | Amount to order or sell | New total | ||
| Column B | ||||||
| Bananas (Row 6) | 5% | 8 | 8% | -3 | 5 | |
| Oranges | 10% | 11 | 11% | -1 | 10 | |
| Avocados | 15% | 15 | 15% | 0 | 15 | |
| Cherries | 30% | 29 | 29% | 1 | 30 | |
| Raspberries | 40% | 37 | 37% | 3 | 40 | |
| 100% | 100 | 100% | 100 |
<tbody>
</tbody>
Assuming ‘Bananas’ are on row 6 and ‘Target’ is in column B, and cell C1 is the dollar amount Big Joe is adding to his monthly inventory:
The formula I’m currently using for the amount to buy or sell is =IF($C$11+$C$1)*B6-C6,0)
If we then look at all the trucks and have a separate calculation for Cherries for each truck, assuming we have a set percentage of the total fruit of each type that we want in total inventory:
| TRUCK TYPE | CHERRIES | TOTAL FRUIT | ACTUAL FRUIT | FRUIT TO ADD |
| Big Truck (A) | 30 | 7.0% | 6.9% | 1 |
| Medium Truck (B) | 20 | 9.0% | 8.8% | 2 |
| Small Truck (C) | 25 | 3.0% | 3.0% | 0 |
| Small Truck (D) | 25 | 5.9% | 5.9% | 0 |
<tbody>
</tbody>
The formulas I am currently using, assuming ‘Big Truck (A)’ is on row 7, ‘Total Fruit’ is column c, actual fruit is column d, fruit to add is column e, and the total inventory of all of the different types of fruit is located in cell B26:
Cell D7 - =IFERROR(B7/$B$26,0)
Cell E7 =(D7-C7)*$B$26*-1
The formula I’m seeking is one that would allow Big Joe to set his inventory targets for each truck, allow him to add inventory to each truck, but then would also allow the ‘Total Fruit’ column on the overall fruit inventory sheet to be an updated target based on the inventory targets set for each individual truck.
Thanks for any help.
