Formula to auto calculate based on other variables

[Scott A]

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

 

[Jacob Skaria]

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)

 

[Jacob Skaria]

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%

 

[Scott A]

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

 

[Scott A]

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

 

[Scott A]

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

 

[Scott A]

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...

 

[Jacob Skaria]

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

 

[Scott A]

Thank you Jacob! That worked perfectly.

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