To have simple equations in a all in one formula

[Fin Fang Foom]

Hi everyone,

I have another dilema. I dont want to use that helper column ( C ). I tried to have a all in one formula. Like:

=(SUM(B2:B28*1.2)/SUM(A2:A28))

But I get a vaule error. The correct answer is: 2644.338

Any ideas how to get the formula work above?

Book2
	A	B	C	D
1		Rate	Rate pcs per hour
2	2	205.1	123.06
3	2	198.17	118.902
4	2	144.46	86.676
5	2	173.5	104.1
6	2	144.46	86.676
7	2	144.46	86.676
8	1	50.93	61.116
9	2	144.46	86.676
10	2	173.96	104.376
11	1	83.78	100.536
12	6	441.73	88.346
13	1	102.55	123.06
14	6	615.28	123.056
15	2	144.46	86.676
16	2	135.34	81.204
17	2	199.28	119.568
18	1	102.55	123.06
19	1	67.36	80.832
20	1	67.36	80.832
21	6	441.73	88.346
22	2	144.46	86.676
23	2	126.72	76.032
24	8	820.4	123.06
25	8	820.4	123.06
26	16	960	72
27	2	144.46	86.676
28	1	102.55	123.06
29			2644.338	#VALUE!
Sheet1

 

[almagg]

you were very close:

=SUM((B2:B28)*1.2/(A2:A28))

plus this is an array formula.
once you type in the cell you have to press:

CTRL+SHIFT+ENTER

this will put brackets around the whole expression.
note: putting brackets manually does not do it.

 

[ravishankar]

HI
you can't have a formula that references the same cell (circular reference)
You can use a macro. It will overwrite col B. If that is OK, paste the following codes in the macro window ( alt f8)

x= cells(rows.count,1).end(xlUp).row
for a = 1 to x
cells(a,2) = cells(a,2)*1.2/cells(a,1)
next a

Try it on a copy of your data before you use it.
try =SUM(B2:B28)*1.2/SUM(A2:A28) to get the correct total.
Ravi

 

[Fin Fang Foom]

you were very close:

=SUM((B2:B28)*1.2/(A2:A28))

plus this is an array formula.
once you type in the cell you have to press:

CTRL+SHIFT+ENTER

this will put brackets around the whole expression.
note: putting brackets manually does not do it.

Thank You almagg!