|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 22nd, 2009, 08:28 AM
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
June 22nd, 2009, 08:42 AM
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
June 22nd, 2009, 08:44 AM
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
June 22nd, 2009, 08:56 AM
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
June 22nd, 2009, 08:56 AM
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
June 22nd, 2009, 08:57 AM
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
June 22nd, 2009, 09:04 AM
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
June 22nd, 2009, 09:45 AM
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
June 22nd, 2009, 03:10 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
