| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to auto calculate based on other variables
Hello,
I am trying to come up with a formula to calculate costs based on another set of values. Cell C7 is automaticaly inserted into the spreadsheet off of other worksheets. What I would like to do is put in the type of work (1-5) in cell C6 and have it calculate C8-C10 based on the type and what the materials costs are in C7 (for type 1 it would be 25% of the total cost). I have the set of values to do the calculations for the type (C6) in cells F5-J10. B C 6 Project Type: 1 7 Project Materials: $100.00 8 Project Labor: ? 9 Project Comp/Ins: ? 10 Project O & P: ? 11 Project Total: (sum of C7-C10) F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% Is there any way to do this with a forluma? Scott A |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to auto calculate based on other variables
Assuming the set of values are in Sheet1. try the below formula....which uses
VLOOKUP... In C7 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,2,FALSE) In C8 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,3,FALSE) In C9 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,4,FALSE) In C10 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,5,FALSE) In C11 =SUM(c7:c10) -- If this post helps click Yes --------------- Jacob Skaria "Scott A" wrote: Hello, I am trying to come up with a formula to calculate costs based on another set of values. Cell C7 is automaticaly inserted into the spreadsheet off of other worksheets. What I would like to do is put in the type of work (1-5) in cell C6 and have it calculate C8-C10 based on the type and what the materials costs are in C7 (for type 1 it would be 25% of the total cost). I have the set of values to do the calculations for the type (C6) in cells F5-J10. B C 6 Project Type: 1 7 Project Materials: $100.00 8 Project Labor: ? 9 Project Comp/Ins: ? 10 Project O & P: ? 11 Project Total: (sum of C7-C10) F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% Is there any way to do this with a forluma? Scott A |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to auto calculate based on other variables
The below set of values in Sheet2..
F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Assuming the set of values are in Sheet1. try the below formula....which uses VLOOKUP... In C7 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,2,FALSE) In C8 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,3,FALSE) In C9 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,4,FALSE) In C10 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,5,FALSE) In C11 =SUM(c7:c10) -- If this post helps click Yes --------------- Jacob Skaria "Scott A" wrote: Hello, I am trying to come up with a formula to calculate costs based on another set of values. Cell C7 is automaticaly inserted into the spreadsheet off of other worksheets. What I would like to do is put in the type of work (1-5) in cell C6 and have it calculate C8-C10 based on the type and what the materials costs are in C7 (for type 1 it would be 25% of the total cost). I have the set of values to do the calculations for the type (C6) in cells F5-J10. B C 6 Project Type: 1 7 Project Materials: $100.00 8 Project Labor: ? 9 Project Comp/Ins: ? 10 Project O & P: ? 11 Project Total: (sum of C7-C10) F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% Is there any way to do this with a forluma? Scott A |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to auto calculate based on other variables
Thnaks Jacob,
They are all on the same sheet in this case. Also, C7 is already determined and is what everything else is calculated from. Just think of it like I am manually inputing $100.00 into C7 and IF if is type "1" then C7 would equal 25% of the total and the formula would need to calculate cels C8-C10 (C8=35%, C9=15%, C10=25%) for me. Does that help of muddy the waters? LOL "Jacob Skaria" wrote: The below set of values in Sheet2.. F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Assuming the set of values are in Sheet1. try the below formula....which uses VLOOKUP... In C7 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,2,FALSE) In C8 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,3,FALSE) In C9 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,4,FALSE) In C10 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,5,FALSE) In C11 =SUM(c7:c10) -- If this post helps click Yes --------------- Jacob Skaria "Scott A" wrote: Hello, I am trying to come up with a formula to calculate costs based on another set of values. Cell C7 is automaticaly inserted into the spreadsheet off of other worksheets. What I would like to do is put in the type of work (1-5) in cell C6 and have it calculate C8-C10 based on the type and what the materials costs are in C7 (for type 1 it would be 25% of the total cost). I have the set of values to do the calculations for the type (C6) in cells F5-J10. B C 6 Project Type: 1 7 Project Materials: $100.00 8 Project Labor: ? 9 Project Comp/Ins: ? 10 Project O & P: ? 11 Project Total: (sum of C7-C10) F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% Is there any way to do this with a forluma? Scott A |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to auto calculate based on other variables
Thanks Jacob,
They are all on the same sheet in this case. Also, C7 is already determined and is what everything else is calculated from. Just think of it like I am manually inputing $100.00 into C7 and IF if is type "1" then C7 would equal 25% of the total and the formula would need to calculate cels C8-C10 (C8=35%, C9=15%, C10=25%) for me. Does that help of muddy the waters? LOL "Jacob Skaria" wrote: The below set of values in Sheet2.. F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Assuming the set of values are in Sheet1. try the below formula....which uses VLOOKUP... In C7 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,2,FALSE) In C8 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,3,FALSE) In C9 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,4,FALSE) In C10 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,5,FALSE) In C11 =SUM(c7:c10) -- If this post helps click Yes --------------- Jacob Skaria "Scott A" wrote: Hello, I am trying to come up with a formula to calculate costs based on another set of values. Cell C7 is automaticaly inserted into the spreadsheet off of other worksheets. What I would like to do is put in the type of work (1-5) in cell C6 and have it calculate C8-C10 based on the type and what the materials costs are in C7 (for type 1 it would be 25% of the total cost). I have the set of values to do the calculations for the type (C6) in cells F5-J10. B C 6 Project Type: 1 7 Project Materials: $100.00 8 Project Labor: ? 9 Project Comp/Ins: ? 10 Project O & P: ? 11 Project Total: (sum of C7-C10) F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% Is there any way to do this with a forluma? Scott A |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to auto calculate based on other variables
Thanks Jacob,
They are all on the same sheet in this case. Also, C7 is already determined and is what everything else is calculated from. Just think of it like I am manually inputing $100.00 into C7 and IF if is type "1" then C7 would equal 25% of the total and the formula would need to calculate cels C8-C10 (C8=35%, C9=15%, C10=25%) for me. Does that help of muddy the waters? LOL "Jacob Skaria" wrote: The below set of values in Sheet2.. F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Assuming the set of values are in Sheet1. try the below formula....which uses VLOOKUP... In C7 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,2,FALSE) In C8 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,3,FALSE) In C9 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,4,FALSE) In C10 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,5,FALSE) In C11 =SUM(c7:c10) -- If this post helps click Yes --------------- Jacob Skaria "Scott A" wrote: Hello, I am trying to come up with a formula to calculate costs based on another set of values. Cell C7 is automaticaly inserted into the spreadsheet off of other worksheets. What I would like to do is put in the type of work (1-5) in cell C6 and have it calculate C8-C10 based on the type and what the materials costs are in C7 (for type 1 it would be 25% of the total cost). I have the set of values to do the calculations for the type (C6) in cells F5-J10. B C 6 Project Type: 1 7 Project Materials: $100.00 8 Project Labor: ? 9 Project Comp/Ins: ? 10 Project O & P: ? 11 Project Total: (sum of C7-C10) F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% Is there any way to do this with a forluma? Scott A |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to auto calculate based on other variables
The other thing is if I input a "2" in the C6 cell then it would calculate
C8-C10 based on the set of values as listed in cells G7-J7, and so on... "Scott A" wrote: Hello, I am trying to come up with a formula to calculate costs based on another set of values. Cell C7 is automaticaly inserted into the spreadsheet off of other worksheets. What I would like to do is put in the type of work (1-5) in cell C6 and have it calculate C8-C10 based on the type and what the materials costs are in C7 (for type 1 it would be 25% of the total cost). I have the set of values to do the calculations for the type (C6) in cells F5-J10. B C 6 Project Type: 1 7 Project Materials: $100.00 8 Project Labor: ? 9 Project Comp/Ins: ? 10 Project O & P: ? 11 Project Total: (sum of C7-C10) F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% Is there any way to do this with a forluma? Scott A |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to auto calculate based on other variables
Try the below in C8,C9 and C10. Inputs would be C6 and C7
In C8 =$C$7/VLOOKUP($C$6,$F$6:$J$10,2,FALSE)*VLOOKUP($C$6,$F$6 :$J$10,3,FALSE) In C9 =$C$7/VLOOKUP($C$6,$F$6:$J$10,2,FALSE)*VLOOKUP($C$6,$F$6 :$J$10,4,FALSE) In C10 =$C$7/VLOOKUP($C$6,$F$6:$J$10,2,FALSE)*VLOOKUP($C$6,$F$6 :$J$10,5,FALSE) If this post helps click Yes --------------- Jacob Skaria "Scott A" wrote: Thnaks Jacob, They are all on the same sheet in this case. Also, C7 is already determined and is what everything else is calculated from. Just think of it like I am manually inputing $100.00 into C7 and IF if is type "1" then C7 would equal 25% of the total and the formula would need to calculate cels C8-C10 (C8=35%, C9=15%, C10=25%) for me. Does that help of muddy the waters? LOL "Jacob Skaria" wrote: The below set of values in Sheet2.. F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Assuming the set of values are in Sheet1. try the below formula....which uses VLOOKUP... In C7 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,2,FALSE) In C8 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,3,FALSE) In C9 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,4,FALSE) In C10 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,5,FALSE) In C11 =SUM(c7:c10) -- If this post helps click Yes --------------- Jacob Skaria "Scott A" wrote: Hello, I am trying to come up with a formula to calculate costs based on another set of values. Cell C7 is automaticaly inserted into the spreadsheet off of other worksheets. What I would like to do is put in the type of work (1-5) in cell C6 and have it calculate C8-C10 based on the type and what the materials costs are in C7 (for type 1 it would be 25% of the total cost). I have the set of values to do the calculations for the type (C6) in cells F5-J10. B C 6 Project Type: 1 7 Project Materials: $100.00 8 Project Labor: ? 9 Project Comp/Ins: ? 10 Project O & P: ? 11 Project Total: (sum of C7-C10) F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% Is there any way to do this with a forluma? Scott A |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to auto calculate based on other variables
Thank you Jacob! That worked perfectly.
"Jacob Skaria" wrote: Try the below in C8,C9 and C10. Inputs would be C6 and C7 In C8 =$C$7/VLOOKUP($C$6,$F$6:$J$10,2,FALSE)*VLOOKUP($C$6,$F$6 :$J$10,3,FALSE) In C9 =$C$7/VLOOKUP($C$6,$F$6:$J$10,2,FALSE)*VLOOKUP($C$6,$F$6 :$J$10,4,FALSE) In C10 =$C$7/VLOOKUP($C$6,$F$6:$J$10,2,FALSE)*VLOOKUP($C$6,$F$6 :$J$10,5,FALSE) If this post helps click Yes --------------- Jacob Skaria "Scott A" wrote: Thnaks Jacob, They are all on the same sheet in this case. Also, C7 is already determined and is what everything else is calculated from. Just think of it like I am manually inputing $100.00 into C7 and IF if is type "1" then C7 would equal 25% of the total and the formula would need to calculate cels C8-C10 (C8=35%, C9=15%, C10=25%) for me. Does that help of muddy the waters? LOL "Jacob Skaria" wrote: The below set of values in Sheet2.. F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Assuming the set of values are in Sheet1. try the below formula....which uses VLOOKUP... In C7 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,2,FALSE) In C8 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,3,FALSE) In C9 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,4,FALSE) In C10 =VLOOKUP($C$6,Sheet2!$F$6:$J$10,5,FALSE) In C11 =SUM(c7:c10) -- If this post helps click Yes --------------- Jacob Skaria "Scott A" wrote: Hello, I am trying to come up with a formula to calculate costs based on another set of values. Cell C7 is automaticaly inserted into the spreadsheet off of other worksheets. What I would like to do is put in the type of work (1-5) in cell C6 and have it calculate C8-C10 based on the type and what the materials costs are in C7 (for type 1 it would be 25% of the total cost). I have the set of values to do the calculations for the type (C6) in cells F5-J10. B C 6 Project Type: 1 7 Project Materials: $100.00 8 Project Labor: ? 9 Project Comp/Ins: ? 10 Project O & P: ? 11 Project Total: (sum of C7-C10) F G H I J 5 Type Materials Labor Comp/Ins O&P 6 1 25% 35% 15% 25% 7 2 20% 30% 15% 35% 8 3 30% 30% 15% 25% 9 4 15% 45% 15% 25% 10 5 25% 40% 15% 20% Is there any way to do this with a forluma? Scott A |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I calculate maximum with variables. | Excel Worksheet Functions | |||
| formula calculate charges based on hours | Excel Discussion (Misc queries) | |||
| I need a formula to calculate rates based on current age | Excel Worksheet Functions | |||
| any formula to auto calculate 1st-12th is 12 days pls? | Excel Discussion (Misc queries) | |||
| formula to calculate a column based on dates in a different colum. | Excel Worksheet Functions | |||
![Scott A[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
