icytuvi
New Member
- Joined
- Feb 2, 2020
- Messages
- 22
- Office Version
- 2013
- Platform
- Windows
Hello there,
I have a excel problem that I need your support in solving this.
- We have products using paint mixture which have blending ratio change over seasons (summer & winter) (table 1)
- The unit cost of paints are given by month corresponding to specific season (table 2).
- Now to calculate the cost of product (table 3)
Could you please suggest if there is any formula that help me not to do the multiplication manually one by one?
I have a excel problem that I need your support in solving this.
- We have products using paint mixture which have blending ratio change over seasons (summer & winter) (table 1)
- The unit cost of paints are given by month corresponding to specific season (table 2).
- Now to calculate the cost of product (table 3)
Could you please suggest if there is any formula that help me not to do the multiplication manually one by one?
Sample.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
2 | Table 1: Color blending ratio of products | ||||||||||||||
3 | Blending % | Winter | Summer | ||||||||||||
4 | Red | Yellow | Green | Red | Yellow | Green | |||||||||
5 | Product 1 | 25% | 30% | 45% | 50% | 30% | 20% | ||||||||
6 | Product 2 | 30% | 28% | 43% | 55% | 28% | 18% | ||||||||
7 | Product 3 | 35% | 25% | 40% | 60% | 25% | 15% | ||||||||
8 | Product 4 | 40% | 23% | 38% | 65% | 23% | 13% | ||||||||
9 | Product 5 | 45% | 20% | 35% | 70% | 20% | 10% | ||||||||
10 | Product 6 | 50% | 18% | 33% | 75% | 18% | 7% | ||||||||
11 | |||||||||||||||
12 | Table 2: Cost of paint | ||||||||||||||
13 | Unit cost | Winter | Winter | Winter | Summer | Summer | Summer | Summer | Summer | Summer | Winter | Winter | Winter | ||
14 | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |||
15 | Red | 50 | 55 | 60 | 65 | 70 | 75 | 67.5 | 60 | 52.5 | 45 | 37.5 | 30 | ||
16 | Yellow | 20 | 25 | 30 | 35 | 40 | 45 | 37.5 | 30 | 22.5 | 15 | 7.5 | 0 | ||
17 | Green | 30 | 35 | 40 | 45 | 50 | 55 | 47.5 | 40 | 32.5 | 25 | 17.5 | 10 | ||
18 | |||||||||||||||
19 | Table 3: To calculate cost of products | ||||||||||||||
20 | Cost Amount | Winter | Winter | Winter | Summer | Summer | Summer | Summer | Summer | Summer | Winter | Winter | Winter | ||
21 | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |||
22 | Product 1 | 32 | |||||||||||||
23 | Product 2 | ||||||||||||||
24 | Product 3 | ||||||||||||||
25 | Product 4 | ||||||||||||||
26 | Product 5 | ||||||||||||||
27 | Product 6 | ||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B22 | B22 | =B5*B15+C5*B16+D5*B17 |