Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula required | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Formula Help required | Excel Discussion (Misc queries) | |||
Formula Help Required | Excel Discussion (Misc queries) | |||
Help Required With IF AND Formula | Excel Worksheet Functions |