Using SUMIF based on a condition

Mary --

Just to make sure: Do you want to really sum the percentages or average them?

And, Could you give the exact range where you have these raw scores interspersed with percentages?

Aladin

=======

Assuming your data is in A1 to A8 use =SUMIF(A1:A8,"<1",A1:A8). If you want averages divide the above by the COUNTIF(A1:A8,"<1")

> Assuming your data is in A1 to A8 use =SUMIF(A1:A8,"<1",A1:A8). If you want averages divide the above by the COUNTIF(A1:A8,"<1")

In practice, it's a solution which is good enough.

What happens if one of the raw scores is 0 or 0.5?

========

You can do this with an array formula. If your row of scores is called "scores", this formula will sum every other entry starting with the second one (i.e, the first percentage):

{=SUM(IF(MOD(COLUMN(scores),2)=0,scores,0))}

To enter an array formula, type this formula without the brackets and hit Ctrl-shft-Enter.

If you want the average, substitute Average for Sum.

(This solution assumes that the first raw score is in an odd-numbered column (e.g, the first column, column A, of the spreadsheet. If the first column is an odd column, substitute =1 for =0. Alternatively, name the first raw score "scores1" and use this formula:

{=SUM(IF(MOD(COLUMN(scores)-COLUMN(scores1)+1,2)=0,scores,0))}

Then you can put the scores anywhere you want and the formula will adjust.

Summing or averaging percentage values in every other row

I should have proposed what I had in mind instead of questioning the OP where the data start and whether the percentages must be averaged or summed. By the way, I'd go for Neil's proposal if it's guaranteed that there will be no RAW scores below 1.

Otherwise, I'd suggest using an array formula or SUMPRODUCT formula using Boolean terms (no IFs):

If the first RAW score of the data start in an UNEVEN numbered row, use

either ordinarily entered

=SUMPRODUCT((MOD(ROW(A1:A8),2)=0)*(A1:A8))/MAX(1,(COUNT(A1:A8)/2))

or array-entered

{=SUM((MOD(ROW(A1:A8),2)=0)*(A1:A8))/MAX(1,(COUNT(A1:A8)/2))}

If the first RAW score of the data start in an EVEN numbered row, use

either ordinarily entered

=SUMPRODUCT((MOD(ROW(A2:A9),2)<>0)*(A2:A9))/(MAX(1,(COUNT(A2:A9)/2))

or array-entered

{=SUM((MOD(ROW(A2:A9),2)<>0)*(A2:A9))/(MAX(1,(COUNT(A2:A9)/2))}

If summing is the goal, drop the demonitor from the above formulas.

==========

Actually you are right - I do want to average them - that's why I need to convert raw scores to percentages. There will be multiple rows i.e. one for each student - can be anywhere on the spreadsheet - don't care!

Thanks Neil I think I tried this and it works fine except when a student has scored 100% and then it doesn't get counted!

Mary --

That is another problem with this otherwise nice formula: it will see 100% as 1.0 which of course does not meet the condition "<1".

I'd suggest using one of the SUMPRODUCT formulas that I suggested.

Cheers.

Aladin

===========

So you have some clever ones and some dumb ones!

Data in the same place, put =IF(ISEVEN(ROW(A2)),A2,"") in B2 and copy down as far as you need, then average (or whatever) column B. You can try ISODD as well for the raw data. > Assuming your data is in A1 to A8 use =SUMIF(A1:A8,"<1",A1:A8). If you want averages divide the above by the COUNTIF(A1:A8,"<1") In practice, it's a solution which is good enough. What happens if one of the raw scores is 0 or 0.5? =