Results 1 to 15 of 18

20071110, 20:38 #1
 Join Date
 Nov 2007
 Posts
 65
 Thanks
 0
 Thanked 0 Times in 0 Posts
cell totals with blank spaces (Excel 2003)
hi there, how can i total up the student number of questions answered (not the amountwhich is the score) just how many students answered question 1 etc, as their are blanks i need a function that will ignore blanks. also can i validate this table so it will enter a "  " sign where no number is inserted automatically, because on this table i am making it so you cant answer over 4 max questions. can you explain how to do this and why so i can understand please.
regards kitty

20071110, 21:24 #2
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: cell totals with blank spaces (Excel 2003)
<P ID="edit" class=small>(Edited by Jezza on 10Nov07 20:24. Typed wrong function)</P>Hi
Simpleset way is to use SUMPRODUCT AND ISNUMBER
In cell G28 of your example type: =SUMPRODUCT(ISNUMBER(G10:G26)+0) and then drag across to cell J28Jerry

20071110, 22:28 #3
 Join Date
 Nov 2007
 Posts
 65
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: cell totals with blank spaces (Excel 2003)
hi there jezza and thank you for your reply, but im still a beginner within excel, can you tell me, as i have never come across "sumproduct" and "isnumber" before, can you in layman's terms explain why i would use these functions, so i can understand why i will need to use them, this will help me with my learning to understand excel as i make notes as to why i have used a formula or functionand and this helps me for future reference so i dont need to disturb you,
thank you kitty

20071110, 22:42 #4
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: cell totals with blank spaces (Excel 2003)
Hi Kitty
The issue surrounds the matter that you wish to count only cells that are not blank. Therefore if you had content such as a number or a text string your could use COUNTA and can be in this syntax =COUNTA(A1:C13).
Due to your requirement to have minus() sign in it which is a text character I was therefore unable to use that function and had to ensure that the cells in the range were in fact a number and there for used ISNUMBER which is boolean and provides a TRUE or FALSE response i.e.
=ISNUMBER(10) is TRUE
=ISNUMBER("") is FALSE
I am therefore only wanting to count if the cell value in the range is a number and will not work with COUNTIF however if I use
SUMPRODUCT
"Multiplies corresponding components in the given arrays, and returns the sum of those products"
It can count the array items that show true in the given range. My adding 0 to the end converts the value shown to a numeric value to allow further calculations if so required. I hope that helpsJerry

20071110, 23:32 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: cell totals with blank spaces (Excel 2003)
Why not just use:
=COUNT(G10:G26)
COUNT only counts numbers. Even if you enter a "" in a cell it will still not be counted...
Steve

20071110, 23:36 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: cell totals with blank spaces (Excel 2003)
As to your second question, I am unclear of how you want to enter the "". Do you just want to enter it at the start when no questions are answered, then you can overwrite it with the number once it is answered.
If you are thinking of something else, how and when do you want to trigger this "validation"? When do you want the "" to be added to the table?
Steve

20071111, 00:18 #7
 Join Date
 Nov 2007
 Posts
 65
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: cell totals with blank spaces (Excel 2003)
hi steve this is exactly what i think i want. i was thinking, but at the same time i have to bear in mind that the table may be up dated or altared, i thought "" was the best fill in for the blank spaces, but is it may be better adding nothing at all, i can add i vaidation so that no more than 4max questions can be answered but im not sure how to do the same validation to prevent a min of four to be answered? would you help me on that to been tryin all nite, im great at word, powerpoint and publisher but pants at this, lol hope you can help, and thanks for the help already
regards kitty

20071111, 00:50 #8
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: cell totals with blank spaces (Excel 2003)
My apologies Kitty, I was totally over complicating the answer, I was assuming something completely different, of course COUNT is the answer <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
It does not matter if you change the results, add new results or even delete results the total will change as you make the changes.
Hoping to redeem myself here <img src=/S/grin.gif border=0 alt=grin width=15 height=15> . You could use Conditional Formatting here.
I apologise, it is late here and I am struggling with your English , when you say "to prevent a min of four to be answered", I am assuming you want them to answer 4 or more, it my assumption is correct then:
1) Highlight Cell F10
2) Click Format  Conditioning Format...
3) Change the Window so that Condition 1 is Formula is and type in the following:
=COUNT($G$10:$J$10)<3
4) Change colour to Red
4) Repeat but with this formula =COUNT($G$10:$J$10)<3
You can use the format Paiinter now to copy the formatting to the other student cellsJerry

20071111, 11:39 #9
 Join Date
 Nov 2007
 Posts
 65
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: cell totals with blank spaces (Excel 2003)
Hi Jerry, sorry about my rubbish english, that was down to a few glasses of wine lol.
I have made the changes as you can see in the attached spreadsheet, and used the format painter, however when igo to ajust the data in cells I14 to O36 it doesnt change red. and even though the rows are set so 4 max and 4 min can be entered as well as adding an error code, i can still get round answering more than 4 entries and if i enter less than 4 as i said its not going red. how can i force these changes, especially so that you cant enter more than 4 entries.
Thanks Kitty

20071111, 12:01 #10
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: cell totals with blank spaces (Excel 2003)
Hi Kitty
We just have to change the values to relate and not absolute, like so
=COUNT($I11:$O11)<4
See attachedJerry

20071111, 13:00 #11
 Join Date
 Nov 2007
 Posts
 65
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: cell totals with blank spaces (Excel 2003)
yes thats great jerry, all is working now, except sorting out the rows that are set to 4 max and 4 min, i can still get round answering more than 4 entries how can i force these changes, especially so that you cant enter more than 4 entries.
other wise extra results can be entered in cells, regards kitty

20071111, 13:10 #12
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: cell totals with blank spaces (Excel 2003)
> except sorting out the rows that are set to 4 max and 4 min,
Hi Kitty,
I still don't understand what you mean by this 4 max and 4 min as your rows data is about people. Can you notate a workbook with comments to explain your requirement exactly... I am still confused <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>Jerry

20071111, 13:29 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: cell totals with blank spaces (Excel 2003)
Select<code> I11:O36</code>.
Select Data  Validation...
Change the formula to
<code>
=AND(COUNTA($I11:$O11)<=4,ISNUMBER(I11))
</code>
Click OK.
The original version referred to cell <code>$P11</code> which contains the formula
<code>
=COUNTA(I11:O11)
</code>
but this cell may not yet have been updated when validation occurs, i.e. it may still return 4 when you enter a 5th result. By including the COUNTA formula in the Validation formula itself, you make sure that the correct value is used.

20071111, 14:11 #14
 Join Date
 Nov 2007
 Posts
 65
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: cell totals with blank spaces (Excel 2003)
right i will try and explain better, if you open up the spreadsheet, bare in mind that this spreadsheet is for the tutors use only, not the student, the tutor updates the spreadsheet as and when the student has grade results.
You have students cells H12:H37 each student must answer 4 out of 7 questions this is a requirement to pass the course and so will fail if they answer less than 4 questions. that is what i ment by (4 max and 4 min). in cells I11:O37 are the marks given to the student for their answer under the question heading they choose, this is entered by the tutor, but if accidently the tutor typed in another result this would mess up the true result. so i need to prevent this, and that is why there must be a validation only allowing 4 results of 4 answered questions.
please say this makes sense as i tried Hans answer and it still doesnt work, also i have unhided coloumn P sorry about that i forgot to unhide it when i sent the first attachment, hope this now makes sence?
regards kitty

20071111, 14:21 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: cell totals with blank spaces (Excel 2003)
How about this cond format.
The validation should prevent more than 4, but even if it is, they will be red (as they will with <4). It will only be green with just 4 entries.
Steve