Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Average form cells containing formulas?
I have set up a Markbook which calculates levels for test scores.
In Cell B3 is the test score for an individual student in cell B4 I have the following formula: =IF(B3<6,"2",IF(B3<11,"3", IF(B3<15,"4",IF(B3<20,"5","6")))) This pattern is repeated along the row with alternate test scores and formula cells which give the corresponding level. As each test requires different scores for a given level the numbers vary slightly. I have five test scores (ten cells of data). I would like to average the levels (not the test scores) I have. In the last cell of the row I have entered the formula: =AVERAGE(C3,E3,G3,I3,K3) But all I get is the Div/0 result. I have tried, F2, Ctrl, SHIFT, ENTER for the level cells and the Average cell but this has not worked. There must be an easy way round this? Any help would be appreciated, feeling rather dumb :) |
#2
|
|||
|
|||
Hi!
Try removing the quotes from your values "2", "3", "4", "5", "6" When you enclose numbers in quotes Excel treats it as TEXT and as a result the AVERAGE function ignores TEXT. Biff -----Original Message----- I have set up a Markbook which calculates levels for test scores. In Cell B3 is the test score for an individual student in cell B4 I have the following formula: =IF(B3<6,"2",IF(B3<11,"3", IF(B3<15,"4",IF (B3<20,"5","6")))) This pattern is repeated along the row with alternate test scores and formula cells which give the corresponding level. As each test requires different scores for a given level the numbers vary slightly. I have five test scores (ten cells of data). I would like to average the levels (not the test scores) I have. In the last cell of the row I have entered the formula: =AVERAGE(C3,E3,G3,I3,K3) But all I get is the Div/0 result. I have tried, F2, Ctrl, SHIFT, ENTER for the level cells and the Average cell but this has not worked. There must be an easy way round this? Any help would be appreciated, feeling rather dumb :) . |
#3
|
|||
|
|||
WOW! Works a treat. So simple and so fast.
Knew it must be simple, learn something new everyday. Thanks a great help! :) "Biff" wrote: Hi! Try removing the quotes from your values "2", "3", "4", "5", "6" When you enclose numbers in quotes Excel treats it as TEXT and as a result the AVERAGE function ignores TEXT. Biff -----Original Message----- I have set up a Markbook which calculates levels for test scores. In Cell B3 is the test score for an individual student in cell B4 I have the following formula: =IF(B3<6,"2",IF(B3<11,"3", IF(B3<15,"4",IF (B3<20,"5","6")))) This pattern is repeated along the row with alternate test scores and formula cells which give the corresponding level. As each test requires different scores for a given level the numbers vary slightly. I have five test scores (ten cells of data). I would like to average the levels (not the test scores) I have. In the last cell of the row I have entered the formula: =AVERAGE(C3,E3,G3,I3,K3) But all I get is the Div/0 result. I have tried, F2, Ctrl, SHIFT, ENTER for the level cells and the Average cell but this has not worked. There must be an easy way round this? Any help would be appreciated, feeling rather dumb :) . |
#4
|
|||
|
|||
You're welcome! Thanks for the feedback.
Biff -----Original Message----- WOW! Works a treat. So simple and so fast. Knew it must be simple, learn something new everyday. Thanks a great help! :) "Biff" wrote: Hi! Try removing the quotes from your values "2", "3", "4", "5", "6" When you enclose numbers in quotes Excel treats it as TEXT and as a result the AVERAGE function ignores TEXT. Biff -----Original Message----- I have set up a Markbook which calculates levels for test scores. In Cell B3 is the test score for an individual student in cell B4 I have the following formula: =IF(B3<6,"2",IF(B3<11,"3", IF(B3<15,"4",IF (B3<20,"5","6")))) This pattern is repeated along the row with alternate test scores and formula cells which give the corresponding level. As each test requires different scores for a given level the numbers vary slightly. I have five test scores (ten cells of data). I would like to average the levels (not the test scores) I have. In the last cell of the row I have entered the formula: =AVERAGE(C3,E3,G3,I3,K3) But all I get is the Div/0 result. I have tried, F2, Ctrl, SHIFT, ENTER for the level cells and the Average cell but this has not worked. There must be an easy way round this? Any help would be appreciated, feeling rather dumb :) . . |
#5
|
|||
|
|||
You could also invoke a LOOKUP formula instead of a chain of IFs...
=LOOKUP(B3,{0,2;6,3;11,4;15,5;20,6}) edmdas wrote: WOW! Works a treat. So simple and so fast. Knew it must be simple, learn something new everyday. Thanks a great help! :) "Biff" wrote: Hi! Try removing the quotes from your values "2", "3", "4", "5", "6" When you enclose numbers in quotes Excel treats it as TEXT and as a result the AVERAGE function ignores TEXT. Biff -----Original Message----- I have set up a Markbook which calculates levels for test scores. In Cell B3 is the test score for an individual student in cell B4 I have the following formula: =IF(B3<6,"2",IF(B3<11,"3", IF(B3<15,"4",IF (B3<20,"5","6")))) This pattern is repeated along the row with alternate test scores and formula cells which give the corresponding level. As each test requires different scores for a given level the numbers vary slightly. I have five test scores (ten cells of data). I would like to average the levels (not the test scores) I have. In the last cell of the row I have entered the formula: =AVERAGE(C3,E3,G3,I3,K3) But all I get is the Div/0 result. I have tried, F2, Ctrl, SHIFT, ENTER for the level cells and the Average cell but this has not worked. There must be an easy way round this? Any help would be appreciated, feeling rather dumb :) . |
#6
|
|||
|
|||
Yep that works as well, although I'm not too familar with the LOOKUP
function, need to do some more homework. What I forgot is that some students have missed tests so I have some Zero's in my results. Used this formula: =AVERAGE(IF(MOD(COLUMN(B3:K3),2)=1,IF(B3:K3<0,B3: K3))) Which strangley works. Is there a simpler method using the LOOKUP function? Thanks "Aladin Akyurek" wrote: You could also invoke a LOOKUP formula instead of a chain of IFs... =LOOKUP(B3,{0,2;6,3;11,4;15,5;20,6}) edmdas wrote: WOW! Works a treat. So simple and so fast. Knew it must be simple, learn something new everyday. Thanks a great help! :) "Biff" wrote: Hi! Try removing the quotes from your values "2", "3", "4", "5", "6" When you enclose numbers in quotes Excel treats it as TEXT and as a result the AVERAGE function ignores TEXT. Biff -----Original Message----- I have set up a Markbook which calculates levels for test scores. In Cell B3 is the test score for an individual student in cell B4 I have the following formula: =IF(B3<6,"2",IF(B3<11,"3", IF(B3<15,"4",IF (B3<20,"5","6")))) This pattern is repeated along the row with alternate test scores and formula cells which give the corresponding level. As each test requires different scores for a given level the numbers vary slightly. I have five test scores (ten cells of data). I would like to average the levels (not the test scores) I have. In the last cell of the row I have entered the formula: =AVERAGE(C3,E3,G3,I3,K3) But all I get is the Div/0 result. I have tried, F2, Ctrl, SHIFT, ENTER for the level cells and the Average cell but this has not worked. There must be an easy way round this? Any help would be appreciated, feeling rather dumb :) . |
#7
|
|||
|
|||
The following LOOKUP formula would avoid generating figures for empty
cells or cells with 0 scores... =LOOKUP(B3,{0,"";2.229E-308,2;6,3;11,4;15,5;20,6}) which would allow you to use an ordinary formula for averaging. Does it work for you? edmdas wrote: Yep that works as well, although I'm not too familar with the LOOKUP function, need to do some more homework. What I forgot is that some students have missed tests so I have some Zero's in my results. Used this formula: =AVERAGE(IF(MOD(COLUMN(B3:K3),2)=1,IF(B3:K3<0,B3: K3))) Which strangley works. Is there a simpler method using the LOOKUP function? Thanks "Aladin Akyurek" wrote: You could also invoke a LOOKUP formula instead of a chain of IFs... =LOOKUP(B3,{0,2;6,3;11,4;15,5;20,6}) edmdas wrote: WOW! Works a treat. So simple and so fast. Knew it must be simple, learn something new everyday. Thanks a great help! :) "Biff" wrote: Hi! Try removing the quotes from your values "2", "3", "4", "5", "6" When you enclose numbers in quotes Excel treats it as TEXT and as a result the AVERAGE function ignores TEXT. Biff -----Original Message----- I have set up a Markbook which calculates levels for test scores. In Cell B3 is the test score for an individual student in cell B4 I have the following formula: =IF(B3<6,"2",IF(B3<11,"3", IF(B3<15,"4",IF (B3<20,"5","6")))) This pattern is repeated along the row with alternate test scores and formula cells which give the corresponding level. As each test requires different scores for a given level the numbers vary slightly. I have five test scores (ten cells of data). I would like to average the levels (not the test scores) I have. In the last cell of the row I have entered the formula: =AVERAGE(C3,E3,G3,I3,K3) But all I get is the Div/0 result. I have tried, F2, Ctrl, SHIFT, ENTER for the level cells and the Average cell but this has not worked. There must be an easy way round this? Any help would be appreciated, feeling rather dumb :) . |
#8
|
|||
|
|||
Yes that works for me as well.
Thanks to both of you for your help, you've been great. I will be sharing this around my department, will save a lot of time. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you average text formulas?? | Excel Discussion (Misc queries) | |||
How do you show formulas in certain cells only (not the whole she. | Excel Worksheet Functions | |||
Min/Max formulas using cells with date format | Excel Worksheet Functions | |||
In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) | |||
delete values in several cells without deleting the formulas | Excel Discussion (Misc queries) |