Formula to auto calculate based on other variables

  • Thread starter Thread starter Scott A
  • Start date Start date
S

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
 
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)
 
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%
 
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
 
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
 
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
 
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...
 
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
 
Thank you Jacob! That worked perfectly.

Jacob Skaria said:
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
Click to expand...
 
Back
Top