Formula required
 

I currently have the following populated in a spreadsheet:
A B C
1 Plan Actual
2 Jan $10.00 $10.00
3 Feb $10.00 $10.00
4 Mar $10.00
5 Apr $10.00
6 May $10.00
7 Jun $10.00
8 Jul $10.00
9 Aug $10.00
10Sep $10.00
11Oct $10.00
12Nov $10.00
13Dec $10.00
14YTD $120.00 $20.00
15 17%

The information in B 14 & c14 is a sum of the cells above. What I am
wanting in C15 is c14/c15 as a % but only upto the where column C is
populated. For example, Jan & Feb are populated so I want C15 to be a
(c2+c3)/(B2+b3) which shoud result 100%, but then if C4 is populated with $5
I want C15 to automatically be (c2+c3+c4)/(b2+b3+b4) which should result 83%,
and so on.

Thanks

Formula required
 

Maybe this in c15

=SUM(C2:C13)/SUMPRODUCT((B2:B13)*(C2:C13<""))

Mike

"The Rook" wrote:

I currently have the following populated in a spreadsheet:
A B C
1 Plan Actual
2 Jan $10.00 $10.00
3 Feb $10.00 $10.00
4 Mar $10.00
5 Apr $10.00
6 May $10.00
7 Jun $10.00
8 Jul $10.00
9 Aug $10.00
10Sep $10.00
11Oct $10.00
12Nov $10.00
13Dec $10.00
14YTD $120.00 $20.00
15 17%

The information in B 14 & c14 is a sum of the cells above. What I am
wanting in C15 is c14/c15 as a % but only upto the where column C is
populated. For example, Jan & Feb are populated so I want C15 to be a
(c2+c3)/(B2+b3) which shoud result 100%, but then if C4 is populated with $5
I want C15 to automatically be (c2+c3+c4)/(b2+b3+b4) which should result 83%,
and so on.

Thanks

Formula required
 

"The information in B 14 & c14 is a sum of the cells above. What I am
wanting in C15 is c14/c15 "

You would unfortunately have a circular reference is you want c15 to be
derived from c14/c15

"The Rook" wrote:

I currently have the following populated in a spreadsheet:
A B C
1 Plan Actual
2 Jan $10.00 $10.00
3 Feb $10.00 $10.00
4 Mar $10.00
5 Apr $10.00
6 May $10.00
7 Jun $10.00
8 Jul $10.00
9 Aug $10.00
10Sep $10.00
11Oct $10.00
12Nov $10.00
13Dec $10.00
14YTD $120.00 $20.00
15 17%

The information in B 14 & c14 is a sum of the cells above. What I am
wanting in C15 is c14/c15 as a % but only upto the where column C is
populated. For example, Jan & Feb are populated so I want C15 to be a
(c2+c3)/(B2+b3) which shoud result 100%, but then if C4 is populated with $5
I want C15 to automatically be (c2+c3+c4)/(b2+b3+b4) which should result 83%,
and so on.

Thanks

Formula required
 

Add a helper column by inserting a column before your data (this will shift
your data over one column). In cell A2 enter the following formula and copy
it doen to row 13.
=IF(D2="","",1)
I would hide column A but not required
Change your formula in cell D15 to
=SUMIF(A2:A13,1,D2:D13)/SUMIF(A2:A13,1,C2:C13)
Hope this works for you --





"The Rook" wrote:

I currently have the following populated in a spreadsheet:
A B C
1 Plan Actual
2 Jan $10.00 $10.00
3 Feb $10.00 $10.00
4 Mar $10.00
5 Apr $10.00
6 May $10.00
7 Jun $10.00
8 Jul $10.00
9 Aug $10.00
10Sep $10.00
11Oct $10.00
12Nov $10.00
13Dec $10.00
14YTD $120.00 $20.00
15 17%

The information in B 14 & c14 is a sum of the cells above. What I am
wanting in C15 is c14/c15 as a % but only upto the where column C is
populated. For example, Jan & Feb are populated so I want C15 to be a
(c2+c3)/(B2+b3) which shoud result 100%, but then if C4 is populated with $5
I want C15 to automatically be (c2+c3+c4)/(b2+b3+b4) which should result 83%,
and so on.

Thanks

Formula required
 

Hi,

Here is but another approach:

=C14/SUM(OFFSET(B2,0,0,COUNT(C2:C13)))

--
Cheers,
Shane Devenshire


"The Rook" wrote:

I currently have the following populated in a spreadsheet:
A B C
1 Plan Actual
2 Jan $10.00 $10.00
3 Feb $10.00 $10.00
4 Mar $10.00
5 Apr $10.00
6 May $10.00
7 Jun $10.00
8 Jul $10.00
9 Aug $10.00
10Sep $10.00
11Oct $10.00
12Nov $10.00
13Dec $10.00
14YTD $120.00 $20.00
15 17%

The information in B 14 & c14 is a sum of the cells above. What I am
wanting in C15 is c14/c15 as a % but only upto the where column C is
populated. For example, Jan & Feb are populated so I want C15 to be a
(c2+c3)/(B2+b3) which shoud result 100%, but then if C4 is populated with $5
I want C15 to automatically be (c2+c3+c4)/(b2+b3+b4) which should result 83%,
and so on.

Thanks

Formula required
 

Hi again,

And if you name the range C2:C13 A
and if you make another minor adjustment your formula can shorten to

=C14/SUM(OFFSET(B2,,,COUNT(A)))

--
Thanks,
Shane Devenshire


"The Rook" wrote:

I currently have the following populated in a spreadsheet:
A B C
1 Plan Actual
2 Jan $10.00 $10.00
3 Feb $10.00 $10.00
4 Mar $10.00
5 Apr $10.00
6 May $10.00
7 Jun $10.00
8 Jul $10.00
9 Aug $10.00
10Sep $10.00
11Oct $10.00
12Nov $10.00
13Dec $10.00
14YTD $120.00 $20.00
15 17%

The information in B 14 & c14 is a sum of the cells above. What I am
wanting in C15 is c14/c15 as a % but only upto the where column C is
populated. For example, Jan & Feb are populated so I want C15 to be a
(c2+c3)/(B2+b3) which shoud result 100%, but then if C4 is populated with $5
I want C15 to automatically be (c2+c3+c4)/(b2+b3+b4) which should result 83%,
and so on.

Thanks