Formula Query

[Steve]

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]

hi Steve,

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

--
isabelle



Le 2012-08-14 06:49, Steve a écrit :

 

[Steve]

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]

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

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]

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.

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

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.

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

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]

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]

I have attached an example spreadsheet which I hope will help clarify.

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
"view-only" link (aka URL; http://...) in a response here. The following is
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]

thanks - here is the link

http://www.filedropper.com/testformula

Regards

steve

 

[joeu2004]

here is the link
http://www.filedropper.com/testformula

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.

 

[Steve]

Hi joeu and thank you so much for your time to help me.

I havent had a chance to test it yet but will report back here once I
do.

It is really appreciated

thanks again

steve