Hi I am trying to figure out a formula for calculating the demand for a years worth of material being used in several different parts below is the Tables that should help. I think it should be a IF vlookup but I am still new to formulating using the fuctions for both. Please Help!! Thank you! This is a smaller example, The real data sheet is about 100 rows by 10 columns.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
IF(VLook-up maybe?
- Thread starter Sargent12
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 74,140
- Office Version
- 365
- Platform
- Windows
Your description is a bit sparse on details, so it isn't clear to me how this is calculated (remember, while the problem may be familiar to you, it is not to us).
Can you please explain the logic for calculating this, and show us the expected results of your example?
Can you please explain the logic for calculating this, and show us the expected results of your example?
Upvote
0
Upvote
0
Cubist
Well-known Member
- Joined
- Oct 5, 2023
- Messages
- 2,139
- Office Version
- 365
- Platform
- Windows
- MacOS
One option.
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | |||
| 2 | Part 1 | 52 | 52 | 52 | 52 | 52 | 52 | ||
| 3 | Part 2 | 36 | 36 | 36 | 36 | 36 | 36 | ||
| 4 | Part 3 | 0 | 1 | 4 | 5 | 0 | 0 | ||
| 5 | Part 4 | 5 | 5 | 5 | 5 | 5 | 5 | ||
| 6 | Part 5 | 12 | 12 | 12 | 12 | 12 | 12 | ||
| 7 | Part 6 | 24 | 24 | 24 | 24 | 24 | 24 | ||
| 8 | |||||||||
| 9 | Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | |||
| 10 | Material 1 | 25 | 25 | 45 | 20 | 30 | 22 | ||
| 11 | Material 2 | 30 | 15 | ||||||
| 12 | Material 3 | 15 | 20 | 40 | |||||
| 13 | Material 4 | 40 | 20 | 30 | 30 | 30 | |||
| 14 | |||||||||
| 15 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | |||
| 16 | Material 1 | 3,188 | 3,233 | 3,368 | 3,413 | 3,188 | 3,188 | ||
| 17 | Material 2 | 1,560 | 1,575 | 1,620 | 1,635 | 1,560 | 1,560 | ||
| 18 | Material 3 | 1,120 | 1,120 | 1,120 | 1,120 | 1,120 | 1,120 | ||
| 19 | Material 4 | 3,670 | 3,700 | 3,790 | 3,820 | 3,670 | 3,670 | ||
Sheet3 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B16:G19 | B16 | =BYROW(TRANSPOSE(B2:B7)*$B$10:$G$13,LAMBDA(r,SUM(r))) |
| Dynamic array formulas. | ||
Upvote
0
Cubist
Well-known Member
- Joined
- Oct 5, 2023
- Messages
- 2,139
- Office Version
- 365
- Platform
- Windows
- MacOS
On second thought, you can also use MMULT.
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | |||
| 2 | Part 1 | 52 | 52 | 52 | 52 | 52 | 52 | ||
| 3 | Part 2 | 36 | 36 | 36 | 36 | 36 | 36 | ||
| 4 | Part 3 | 0 | 1 | 4 | 5 | 0 | 0 | ||
| 5 | Part 4 | 5 | 5 | 5 | 5 | 5 | 5 | ||
| 6 | Part 5 | 12 | 12 | 12 | 12 | 12 | 12 | ||
| 7 | Part 6 | 24 | 24 | 24 | 24 | 24 | 24 | ||
| 8 | |||||||||
| 9 | Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | |||
| 10 | Material 1 | 25 | 25 | 45 | 20 | 30 | 22 | ||
| 11 | Material 2 | 30 | 0 | 15 | 0 | 0 | 0 | ||
| 12 | Material 3 | 0 | 15 | 0 | 20 | 40 | 0 | ||
| 13 | Material 4 | 40 | 20 | 30 | 30 | 0 | 30 | ||
| 14 | |||||||||
| 15 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | |||
| 16 | Material 1 | 3188 | 3233 | 3368 | 3413 | 3188 | 3188 | ||
| 17 | Material 2 | 1560 | 1575 | 1620 | 1635 | 1560 | 1560 | ||
| 18 | Material 3 | 1120 | 1120 | 1120 | 1120 | 1120 | 1120 | ||
| 19 | Material 4 | 3670 | 3700 | 3790 | 3820 | 3670 | 3670 | ||
Sheet2 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B16:G19 | B16 | =MMULT($B$10:$G$13,B2:B7) |
| Dynamic array formulas. | ||
Upvote
0
