Excel Help

[Mrshan]

I have multiple numbers in one column (I'm counting my grades) and I have their sum in a column next to it. So I need an average number of that column, so it's sum divided by the number of numbers (grades) [ S/n , where S is sum and n count of numbers I have] I have in that one column.
What formula do I use? AVERAGE doesn't work, because it doesn't count the numbers in that one column where I have multiple numbers

Hope you understand ( English is not my native language )

[Enderman]

average = SUM() / COUNT()

[Mrshan]

1 minute ago, Enderman said:

average = SUM() / COUNT()

Nope, shows a #DIV/0! Error

This is the formula I've used to calculate the SUM of that one column, since SUM function didn't count every number in that column:
=SUM(IF(ISERR(VALUE(MID(B12,ROW($B$1:OFFSET($B$1,LEN(B12)-1,0)),1))),0,VALUE(MID(B12,ROW($B$1:OFFSET($B$1,LEN(B12)-1,0)),1))))

[RumSwift]

If the above didn't work, could you post a screenshot of your table?

[Mrshan]

Just now, Rhyss said:

If the above didn't work, could you post a screenshot of your table?

So where it says "Srednja Ocjena" I want my arithmetic sequence
"Zbir" is the SUM of the numbers in the column "Ocjene" 

[Enderman]

That's not a column that's a row...

[Mrshan]

1 hour ago, Enderman said:

That's not a column that's a row...

oh yes... sry my bad
but I want to use this "3 4" column to divide with 7 hahah

[Enderman]

6 minutes ago, Mrshan said:

oh yes... sry my bad
but I want to use this "3 4" column to divide with 7 hahah

Why can't you put the different values into individual cells?

That's how spreadsheets are meant to be used.

[Mrshan]

1 minute ago, Enderman said:

Why can't you put the different values into individual cells?

That's how spreadsheets are meant to be used.

I'm too lazy to change it now, even if it's easy af
I assume I'll do that, have individual cells, since I can't find a solution

[LogicalDrm]

On 11/12/2018 at 9:02 PM, Mrshan said:

I'm too lazy to change it now, even if it's easy af
I assume I'll do that, have individual cells, since I can't find a solution

You'll have to. The way it is now, Excel sees those cells as text/general. You can't have any other symbols in number cell but numbers and comma/dot (which ever you would use as decimal separator). And + or - at start to symbol positive and negative values. Tip regarding that, if you want to show + mark before number, have ' at start of it. Works also if you need to write out formulas. But Excel will then use that cell as text/general.

 

You can have header cell merged from all column headers you need for different grades.

[homeap5]

(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";"")))+1

This formula returns number of grades in your case.

Replace A1 with cell where you have your grades.

And you still can use it the way you like (in single cell with spaces).

 

Don't worry about double spaces or spaces at the beginning or the end - they do not count.

 

Excel is really powerful program and if something can't be done using formula, there is always VBA.

[Mrshan]

18 hours ago, homeap5 said:

(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";"")))+1

This formula returns number of grades in your case.

Replace A1 with cell where you have your grades.

And you still can use it the way you like (in single cell with spaces).

 

Don't worry about double spaces or spaces at the beginning or the end - they do not count.

 

Excel is really powerful program and if something can't be done using formula, there is always VBA.

TY for that but I ended up making a cell by cell Worksheet, where every grade had it's cell