Dividing a cell based on another cell

[danjoles1]

[TABLE="width: 398"]
<tbody>[TR]
[TD]I am trying to write a formula to calculate cost per foot (cell E13)
I want the total in cell E12 to be divided by cell C8 if it has data if not move to cell C9, do this through C11.

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD]Quantity[/TD]
[TD]Cost[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Corner post assembly[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]720[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]"H" assembly[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]560[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Wood line post[/TD]
[TD][/TD]
[TD="align: right"]21.88[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]T post[/TD]
[TD="align: right"]860[/TD]
[TD="align: right"]4.85[/TD]
[TD="align: right"]4171[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fiberglass post[/TD]
[TD][/TD]
[TD="align: right"]7.28[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3 Strand electric[/TD]
[TD][/TD]
[TD="align: right"]0.09[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3 Strand Barbless[/TD]
[TD][/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4 Strand Barb[/TD]
[TD][/TD]
[TD="align: right"]0.21[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5 Strand Barb[/TD]
[TD="align: right"]14000[/TD]
[TD="align: right"]0.26[/TD]
[TD="align: right"]3640[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Total cost of material:[/TD]
[TD="align: right"]9091[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Matrerial cost per ft:[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Tim_Excel_]

I am trying to write a formula to calculate cost per foot (cell E13) I want the total in cell E12 to be divided by cell C8 if it has data if not move to cell C9, do this through C11.

<tbody>
</tbody>

I am assuming you mean to say, if C8 has data?

=IFS($C$8<>"",$E$12/$C$8,$C$9<>"", $E$12/$C$9,$C$10<>"", $E$12/$C$10, $C$11<>"",$E$12/$C$11,)

 

[danjoles1]

Yes, I copied that and it gives me a name error.

 

[Tim_Excel_]

Remove the last comma in the formula. Typing error.

 

[danjoles1]

Well I tried that, and I removed spaces after the coma's and still receiving the name error. the code looks like this now...
=IFS($C$8<>"",$E$12/$C$8,$C$9<>"",$E$12/$C$9,$C$10<>"",$E$12/$C$10,$C$11<>"",$E$12/$C$11)

 

[Tim_Excel_]

The IFS function is not available for all excel versions, thus your error

You can convert it to nested IF functions (IF($C$8<>"",$E$12/$C$8, IF()) and so on

 

[danjoles1]

The IFS function is not available for all excel versions, thus your error

You can convert it to nested IF functions (IF($C$8<>"",$E$12/$C$8, IF()) and so on

I have tried a couple different ways.

=(IF($C$8<>"",$E$12/$C$8,$C$9<>"",IF($E$12/$C$9,$C$10<>"",IF($E$12/$C$10,$C$11<>"",IF($E$12/$C$11)))))
=IF($C$8<>"",$E$12/$C$8,$C$9<>"",IF($E$12/$C$9,$C$10<>"",IF($E$12/$C$10,$C$11<>"",IF($E$12/$C$11))))
=IF($E$12/$C$8,$C$9<>"",IF($E$12/$C$9,$C$10<>"",IF($E$12/$C$10,$C$11<>"",IF($E$12/$C$11))))
=IF($C$8<>"",$E$12/$C$8,$C$9<>"",($E$12/$C$9,$C$10<>"",($E$12/$C$10,$C$11<>"",($E$12/$C$11))))
=IF($E$12/$C$8,$C$9<>"",($E$12/$C$9,$C$10<>"",($E$12/$C$10,$C$11<>"",($E$12/$C$11))))

Its not accepting these

 

[Tim_Excel_]

I don't want to give you the full formula as I don't think that will teach you how formulas work, and I don't think you're showing incentive to learn how they do..

When you type =IF() in the formula bar (or any other valid formula for that instance), you'll be able to open the Function Arguments window by clicking on the fx symbol left of the formula bar. Here you will be able to see and fill in all arguments for the formula.

For the IF() function, there are three arguments:
1. the logical test
2. the value if the logical test is true
3. the value if the logical test is false

So, IF(1=1, "a", "b") will return "a" because 1 is always equal to 1.

In your case you want to test whether cell C8 has a value. If it does, you want to divide E12 by that number. If it does not, you want to redo the IF() function for cell C9 (see the three arguments of the IF() function?). So, you end up with a nested IF() function.

 

[FormR]

[TABLE="width: 398"]
<tbody>[TR]
[TD]I am trying to write a formula to calculate cost per foot (cell E13)
I want the total in cell E12 to be divided by cell C8 if it has data if not move to cell C9, do this through C11.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi, if only one cell in C8 to C11 can ever be populated then you could simply use something like.

Excel 2013/2016
	B	C	D	E
2	Item	Quantity	Cost	Total
3	Corner post assembly	8	90	720
4	H assembly	10	56	560
5	Wood line post		21.88	0
6	T post	860	4.85	4171
7	Fiberglass post		7.28	0
8	3 Strand electric		0.09	0
9	3 Strand Barbless		0.16	0
10	4 Strand Barb		0.21	0
11	5 Strand Barb	14000	0.26	3640
12	Total cost of material:			9091
13	Matrerial cost per ft:			0.649357
Sheet1

Cell Formulas
Range	Formula
E13	=E12/SUM(C8:C11)