General Betting
There is currently 1 person viewing this thread.
05 Oct 10
16:57
Joined:
Date Joined:
11 Mar 02
wife is doing a spreadsheet , she has 2 columns
column A is the date and the same date may have 100s numerical values entries in column B, she wants in column C to average all values in B that correspond to the sme date in column A, what formula would she need to give this average on all the rowas corresponding to each individual date, or can this not be done? thanks.
column A is the date and the same date may have 100s numerical values entries in column B, she wants in column C to average all values in B that correspond to the sme date in column A, what formula would she need to give this average on all the rowas corresponding to each individual date, or can this not be done? thanks.
sort by:
Show
per page
Replies: 30
|
By:
Copy and paste this in C1
=AVERAGEIF(A$1:A200,A1,B$1:B200) Change the 200(both) in the formula to how many rows you have. Fill down the formula for how many rows you have. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Slight correction to formula I left out the dollar sign before each 200
=AVERAGEIF(A$1:A$200,A1,B$1:B$200) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
blimey, a spreadsheet to work out the Hanagan winnings - respect!
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
thanks will tell her to try it, cheers m8
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
CM thanks, my wife used the formula
=AVERAGEIF(A$1:A$200,A1,B$1:B$200) and #NAME? was returned in each cell strangly if she changed the formula to =SUMIF(A$1:A$200,A1,B$1:B$200) it then worked perfectly in totaling the values corresponding to each individual date Do you have any ideas why it wont work for her with the AVERAGE? Very frustrating as would solve a problem of manually averaging and inputting values for each date. cheers | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Averageif probably wasn't available in the version of excel she is using. She could have another column =COUNTIF(A$1:A$200,B$1:B$200)
and then a third column dividing the result into the SUMIF column. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Make that =COUNTIF(A$1:A$200,A$1:A$200) or =COUNTIF(A$1:A$200,A1)
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
As Ian said you probably have an earlier excel version that does not support AVERAGEIF
If you have an earlier version ~ this formula will do the same thing as AVERAGEIF =SUMIF(A$1:A$200,A1,B$1:B$200)/COUNTIF(A$1:A$200,A1) Cheers | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Excellent now is there a way of retreiving MEDIANIF from the same data in excel 2003?
i have had a look on google can't find a solution, thought i would pick your brains, thanks | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
There is no MEDIANIF()in excel you can manipulate median Using MEDIAN(IF(.....
Just use the formula in my last post much easier | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
she is concerned that a couple of extreme values could make increase the average and not be a true representation, so that is why she is hoping to use median as well to see which works better, is there no way of getting the median in this example?
cheers | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Hi there, I have a similar problem to MANCHESTERSKYTRAIN's except I'm working with OPEN OFFICE not EXCEL
I Have a table like this and I want to be able to total up the result of each days bets in Column C
I've tried entering the formula =SUMIF(A2:A15;A2;B2:B15) in Cell C2 but it does not seem to operate as people are suggesting it operates in EXCEL. ( it appears to be summing up the whole B column reagardless of the date ) Anyone got any ideas on how to do this in OpenOffice ? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
I only know of 2 ways to do what you want.
As it requires an array formula the easiest way is to find 2 empty cell at the top of your spreadsheet lets say E1 and F1 are vacant cells in E1 enter the date you wish to know the median of and in Cell F1 enter this formula =MEDIAN(IF(A1:A200=E1,B1:B200)) Select F1, Click in the formula bar so the insertion point is blinking and then~ To enter the array formula, with the shift and control keys held down press enter. You only have to do that the one time. that is where your answer will be. just change the date in E1 to see what the median is for that date. If you really need the answers in Column C on the same rows as the date, one way is to write a function macro. There is probably other ways to do this but I don't know, someone more expert than me might be able to help. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Escapee try in cell C2 and fill down
=IF(A2=A3,"",SUMIF(A$2:A2,A2,B$2:B3)) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Escapee error correction
=IF(A2=A3,"",SUMIF(A$2:A2,A2,B$2:B2)) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Compound
=IF(A2=A3,"",SUMIF(A$2:A2,A2,B$2:B2)) That's give an Err:508 which according to the 'help' is: Missing Brackets And there appears to be no missing brackets in that there are 2 opening and 2 closing. Searching the 'help' for the '$' gives no results and not sure what the Open Office equivilent to '$' is. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Escapee
I needed something similar and Compound Magic's tip works a treat.....
Don't forget to change , to ; for open office.  | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Compound and Top2rated
Thanks... I'm getting there, I had just managed to work out the , and ; switch when Top2Rated posted  Problem I getting now is that it's summing up for each race instead of each day. In column 'A' I have a date "09/07/2010 15:00:00" which I then 'Format Cells' to "09/07/10" but it appears that the =IF(A2=A3... is still seeing the time portion of the date and hence summimg at the race level instead of the day level. Any neat shortcuts to remove the time portion of the date ? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
The $ sign before a cell row or cell column signifies an absolute address without the $ sign means it
is a relative reference. The formula works for me in open office 3.2.1 and I am sure it should work in earlier versions Try entering This formula in just cell C2 and see if it comes up with an error =SUMIF(A$2:A2,A2,B$2:B2) Now try =IF(A2=A3,"",2) and see if that comes up with an error If those are error free retry =IF(A2=A3,"",SUMIF(A$2:A2,A2,B$2:B2)) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
I appreciate your Help Compound it was the Comma's causing the error508 not the $
Any tips on getting the =IF(A2=A3... to ignore the time portion of the date ? Many thanks again | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Ok getting there
Try =IF(DAY(A2);=DAY(A3);"";SUMIF(A$2:A2;A2;B$2:B2)) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Full of errors today
should be =IF(DAY(A2)=DAY(A3);"";SUMIF(A:A2;A2;B$2:B2)) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
I've got =IF(DAY(A2)=DAY(A3);"";SUMIF(A$2:A2;A2;B$2:B2))
Which differs from your last formula ( as bolded ) and it's producing totals at the expected place in column 'C' but the actual figures are incorrect. ( i.e. don't tally with the test figures I have manually generated using SUM( A2:A38 ) ( which is one days bets ). I still haven't managed to get my head completely round the absolute/relative addressing thingy of the $ symbol and how it works. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
I stuffed up again
=IF(DAY(A2)=DAY(A3);"";SUMIF(A$2:A2;A2;B$2:B2)) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
This is what I'm getting ( with the expected manually generated results in the last column )
I have this in Cell C1 =IF(DAY(A1)=DAY(A2);"";SUMIF(A$1:A1;A1;B$1:B1)) which is then 'filled' down to cell C16 =IF(DAY(A16)=DAY(A17);"";SUMIF(A$1:A16;A16;B$1:B16)) I'm obviously using the $ incorrectly in the SUMIF(... section | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
I can see now that it is only SUMming up the last record of the day and the one prior to it.
i.e. lines 5 and 6: -27.20 + 29.93 = 2.73 lines 10 and 11: 7.91 + -5.83 = 2.08 and line 16 doesn't seem to add up with line 15 and is = -9.92 ?? so close and yet so far | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Ive got it sussed the $ sign is okay the problem is the hidden part of the date changing values (the time part) So a
new formula Must go in cell C2 and fill down =IF(DATE(A2)=DATE(A3);"";SUMIF(D$2:D2;D2;B$2:B2)) Now in Cell D2 this formula ~ you can hide this column if it bothers you once formulas are entered. =LEFT(VALUE(A2),5) Format column D as numbers Now fill down ~ that is cutting the time of that is hidden which is stuffing everything up. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Further testing and hair pulling has revealed that is does indeed work as expected if I manaually delete the time portion of the date
 So it would seem the IF( (DAY(A1)=DAY(A2) is correctly detecting the change of day and ignoring the time portion but for some reason the SUMIF(A$1:A1;A1;B$1:B1) is not ignoring the time portion of the bet and hence only adding up bets for the last race of each day. back to pulling my hair out to work out why. Any suggestions anyone ? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Forget last post this is the real deal!!!!! Its 3am here and i'm making errors
Ive got it sussed the $ sign is okay the problem is the hidden part of the date changing values (the time part) So a new formula Must go in cell C2 and fill down =IF(DAY(A2)=DAY(A3);"";SUMIF(D$2:D2;D2;B$2:B2)) Now in Cell D2 this formula ~ you can hide this column if it bothers you once formulas are entered. =LEFT(VALUE(A2),5) Format column D as numbers Now fill down ~ that is cutting the time of that is hidden which is stuffing everything up. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By:
Thankyou Thankyou Thankyou
It does indeed work ( except for the comma in LEFT(.... ) I had just got there myself in a similar way by using an intermediate column H and setting with =DAY(A1) and then ammending the SUMIF to =IF(H1=H2;"";SUMIF(H$1:H1;H1;B$1:B1)) Thanks again for all your help Compound. Seems a lot of faffing about, Does Excel require a dummy column or can you just use the "format cell" and forget the dummy column ? |