 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Hello,
I have attendance information for a number of groups which is entered in a spreadsheet for the session dates throughout the year. I have a summary worksheet which has an auto sum for the total and =COUNTIF(C4:V4,">.5") to divide this by the number of sessions to give the average. I have conditional formatting on the range so can visually identify the high and low attendance, but would like to have this information in a separate cell so that I can link the summary sheet to include these as well as the average. I would really appreciate any suggestions as to how to do this. It does need to auto update as the attendance for each session is added throughout the year.
Many Thanks 🙂
Struggling to see what you want...
1) I have a summary worksheet which has an auto sum for the total and =COUNTIF(C4:V4,">.5") to divide this by the number of sessions to give the average.
2) I have conditional formatting on the range so can visually identify the high and low attendance,
3) I would like to have this information in a separate cell so that I can link the summary sheet to include these as well as the average.
What information do you want in this separate cell?
What's are the columns you want to work on = are they numeric or characters?
To sum up what are you actually trying to work out?
Hey,
They are numeric. Basically I work for a charity and we run various groups. The spreadsheet has a record of the number of individuals in attendance for each session. There are approximately 20 sessions for the year. Obviously I can work out the total and average attendance and have these values in a specific cell. The summary sheet I have has this information for the seven groups that we run so that you can compare attendance across groups. In addition to this I would like to have the high and low attendance for each group represented alongside the total and average. As this data is entered throughout the year I wondered in there is a formula that can establish the highest and lowest value of a range of cells.
I hope this is a bit clearer, thanks 🙂
=Min & = Max
Sorry to sound thick but would that be =MIN(A1:A10)
Hey, that works great for the max but as the range I am analysing has cell references to auto sums in other sheets any of these with no data in are counted as the min value as the default setting is zero, any ideas?
Prefix the autosums with an IF statement so that instead of blank they return something else? ie "none"
Hey,
I have 🙂 I have share the template in dropbox (link below)
AB Monitoring - TEMPLATE.xlsx (file://SHORTLANDPALMER/Users/palmer/Dropbox/AB%20Monitoring%20-%20TEMPLATE.xlsx)
I would like to represent this information on the summary sheet of the file.
I get paid a lot of money to do that, and I can't see a link 😉
Hey,
I have put the two formulae into the template and here is the link:
file://SHORTLANDPALMER/Users/palmer/Dropbox/AB%20Monitoring%20-%20TEMPLATE%2001.xlsx
Hey,
How would you...
Prefix the autosums with an IF statement so that instead of blank they return something else? ie "none"
=COUNTIF(E4:E103,">0.5", "")
Like this?
=(IF(E4:E103<0.5,"", SUM(E4:E103)))
I don't get what your COUNTIF is for?
If you want an average... wouldn't =AVERAGE(E4:E103) be better?
The countif is give the number of sessions having taken place at any point throughout the year, and I did use it to get the average using basic excel calculations 🙂
OK
=IF(yourformula(blah:blah)=0,"none",yourformula(blah:blah))
Should work, but I'm tired, I don't really know what you want and have been drinking wine to help me forget about excel 🙂
Hey,
Vino Maipo at Sainsbury's, I'll get you some 🙂
Hey,
Is this what you mean:
=IF('BD-LOAF EMPLOYEES'!E3=0,"",'BD-LOAF EMPLOYEES'!E3)
Hey,
I tried to do: =IF(C5:V5=0, "", (MINC5:V5)) so that I don't have to redo all of the links but is does not seem to work.
Link here: file://SHORTLANDPALMER/Users/palmer/Dropbox/AB%20Monitoring%20-%20TEMPLATE%2003.xlsx