Windows 7 - Formula Query

Asked By Steve on 14-Aug-12 06:49 AM
Hi, I have a spreadsheet which is updated daily and I would like to
carry out a caculation based on groups of data for example:


Fields as follows:

Column A                 Column B       Column C
Date and Time          Total             Calc Field

A1 14 Aug 14:20            26           C1
A2 14 Aug 14:20            34           C2 etc
A3 14 Aug 14:20            15
A4 14 Aug 14:20            9
A5 14 Aug 14:20            7
A6 14 Aug 14:50           120
A7 14 Aug 14:50           100
A8 14 Aug 14:50           65
A9 14 Aug 14:50           32
A10 14 Aug 14:50          12


In essence what I would like to be able to do is as follows:


the forumla in the calc field will return the result of B1 divided by
the sum of B1:B5 (because the date and time ranges are the same in
A1:A5

In this example C1 would return 0.29 (2 decimal places)

B1 = 26 / Sum B1:B5 = 91 = 0.29

I would like to then copy the formula down the column so that the
answer to C2 would be B2 divided by the sum of B1:B5 and so on......

Therefore when I copy all the way down and calculate the spreadsheet
the results would be all the way to the bottom so that the formula
would be correctly populated and therefore the  answer to C10 would be
C6 divided by the sum of C6:C10 with the result being 0.36

I hope that makes sense and thanks in advance for any help

Steve




isabelle replied to Steve on 14-Aug-12 08:07 AM
hi Steve,

=B2/SUMPRODUCT(--($A$2:$A$11=A2)*($B$2:$B$11))

--
isabelle



Le 2012-08-14 06:49, Steve a ?crit :
Steve replied to isabelle on 14-Aug-12 10:12 AM
Hi Isabelle and thanks for such a quick reply.

I have tried that formula but it returns a #DIV# result when I copy
down my spreadsheet

I think the problem might be that when I copy the formula down my
spreadsheet the formula no longer works when it gets to the next set
of numbers.

In the example below the calculation should only concern the A1:A5
values but the next set of calculations should then only concern the
A6:A10 values and so on.

when I copy the formula below down my spreadsheet the values shown
below remain static (the ones surrounded by $) when they should change
depending on what the next set of values which are the same in column
A are. So first calc relates to Date and Time = A1 Next calc should
look at the Date and time = A6

I hope that makes sense

Thanks again

Steve
joeu2004 replied to Steve on 14-Aug-12 10:46 AM
Put the following into C1 and copy down:

=B1 / SUMIF($A$1:$A$10,A1,$B$1:$B$10)

Note the purposeful use of "$" in come case, but not in others.

Also note that if the actual range of data goes beyond row 10, you must
change the references to $A$10 and $B$10 accordingly.



----- original posting -----
joeu2004 replied to Steve on 14-Aug-12 11:00 AM
Although that works, the formula can be simplified in either of two ways:

=B2/SUMPRODUCT(--($A$2:$A$11=A2),$B$2:$B$11)

=B2/SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11))

I prefer the first form, when feasible, for a number of technical reasons.



Perhaps because your data does truly begin in row 1 as your wrote, not row
2, __and__ your data goes beyond row 10 or 11.



No.  Isabelle's formula and my SUMIF formula are intended to compare the
current row with the __entire__ column of data.  It is inefficient.  But we
cannot know "a priori" how many rows of data to sum in the denominator.
Steve replied to joeu2004 on 14-Aug-12 11:29 AM
Hi Isabelle and Joeu and thanks for your input.


I have attached an example spreadsheet which I hope will help clarify.
The example I gave initially was just to try and illustrate what I
wanted to do. what I really want is for the formula to repeat by
dragging to the bottom of the column dataset and then for me to hit
the calculate button and let it work for the whole column.

I hope that makes sense

thanks

Steve
joeu2004 replied to Steve on 14-Aug-12 12:28 PM
Good idea.  Unfortunately, I do not see the attachment in my news reader.
That is not an uncommon problem.  I suggestion the following alternative.

Upload an example Excel file (devoid of any private data) that demonstrates
the problem to a file-sharing website and post the "shared", "public" or
a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com
Steve replied to joeu2004 on 14-Aug-12 12:53 PM
thanks - here is the link

http://www.filedropper.com/testformula

Regards

steve
joeu2004 replied to Steve on 14-Aug-12 01:45 PM
Download "testformula modified.xlsx" from
https://www.box.com/s/269a09e4ff285e8b8c7c.

See the "modified" worksheet.  Be sure to read the textboxes on the right.

In a nutshell....

I have inserted columns F through H to demonstrate the formulas.

Column F is my original SUMIF formula.

Column G and H are SUMIFS (with an "s") and SUMPRODUCT alternatives that
obviate the need for Column C, if you prefer.

Since you are using Excel 2007 or later, SUMIFS is preferred.  But if you
need compatibility with Excel 2003, use SUMPRODUCT.

The formulas are basically no different than the ones suggested in this
thread already.  The only difference is that I extended the ranges, which
might be part of the problem you are having in applying the suggestions.

I also noted a mistake in one of your "result" formula, originally in F4,
now in I4.

Also note that the file you uploaded has Manual calculation mode set.

I left that as-is.  But that might create some confusion when you copy
formulas.  They might not be recalculated until you manually calculate by
pressing F9.  Is that intentional?

One final comment....  I notice that column D are rounded to integers due to
formatting.  And the ratios in columns F through H are rounded to 2 decimal
places due to formatting, which matches your original column F, now column
I.

Consequently, the ratios are deceptively equal in some cases; and their
displayed values might not sum to 1.00.

You probably understand the consequences of your format choices.  But I
thought I would bring that to your attention, just in case you do not.