Using if in calculation formula

Using if in calculation formula

by Eric Hagley -
Number of replies: 18
Picture of Particularly helpful Moodlers

I have searched in the forums but can't seem to find an answer and can't seem to get anything to work. I am wondering if is possible to use "if" functions with "sum" functions in formula within the edit calculation feature. For example, I have a category in which I have many quizzes with scores in ranges of 0 to 1000. I want to sum all the scores and then give a total grade of 1 to 10 (for example) to the category. So, for example, if a student gets a sum of 1500 he/she scores 2, if a student gets a sum of 5000 he/she gets 5, if a student gets over 10,000 they get 10 etc. In Libre office the formula might look something like (different numbers here I know) =IF(SUM(A1:E1)=0,"0",IF(SUM(A1:E1)<500,"1",IF(SUM(A1:E1)<1000,"2",IF(SUM(A1:E1)<3000,"3",IF(SUM(A1:E1)<5000,"4",IF(SUM(A1:E1)>=5000,"5"))))))

If I give id numbers to all quizzes can I include them in such a formula? Is there a simpler way to do this? Any ideas greatly appreciated.

In reply to Eric Hagley

Using if in calculation formula

by alberto ro -

Hi,

I'd try setting aggregation type as "sum" or "natural" ( I don't know how is seen in English versions) where I have your quizzes.

After that I'd set an "id" to category eg: "myquizzes" and the formula will set:  

=if(myquizzes,0,if(myquizzes<500,1,if(myquizzes<1000,2,if(myquizzes<3000,3,if([[myquizzes<5000,4,5)))))

In reply to alberto ro

Using if in calculation formula

by Eric Hagley -
Picture of Particularly helpful Moodlers
Very elegant! Thank you indeed. I had not considered giving the whole category the id but it makes perfect sense. I am on my way out now but will try that tomorrow and hope it works well - it would seem it does. Thank you again.
In reply to alberto ro

Using if in calculation formula - not right yet

by Eric Hagley -
Picture of Particularly helpful Moodlers
I have been trying to get this to work without much luck unfortunately. In the gradebook I have a category called reading. I have its Aggregation set as natural. The ID number (after clicking show more in edit category) is RWC. At the bottom of the category in the edit calculation section I have tried both =if(RWC,0,if(RWC<500,1,if(RWC<1000,2,if(RWC<3000,3,if(RWC<4000,4,if(RWC<5000,5))))))
and (as you suggested)
=if(RWC,0,if(RWC<500,1,if(RWC<1000,2,if(RWC<3000,3,if([[RWC<5000,4,5)))))
but neither work. What am I doing wrong? Any ideas greatly appreciated.
In reply to Eric Hagley

Using if in calculation formula - not right yet

by Eric Hagley -
Picture of Particularly helpful Moodlers
Ok, I have now also put the RWC in enclosed double brackets RWC but still no joy
In reply to Eric Hagley

Using if in calculation formula - not right yet

by alberto ro -

That's right, the name of the category has to be between double bracket "" and "".

Please check the range of your category, because if the range is 0-100 , gradebook will scale grades and will never reach above 100. 

Could you give the result of the formula I sent it?

In reply to alberto ro

Using if in calculation formula - not right yet

by Eric Hagley -
Picture of Particularly helpful Moodlers
Thank you kindly Alberto for this,
 
When you say the "range of your category" I am not sure I understand. I have multiple quizzes in the category. I want this category to be worth 10% of the final course grade. Each quiz is given a grade based on the number of words read in the story within the quiz. So if the short story for quiz 1 has 480 words in it, the quiz's grade is 480 (if the student reads the story and completes the questions that follow the story). There will be many stories the students can read. I want them to read as many as possible and see how many words they have read. Based on the number of words they read, they will get a grade - hence the sliding scale. However, the reading category is only a small part of the overall course. 
When I put =if(||RWS||,0,if(||RWS||<500,1,if(||RWS||<1000,2,if(||RWS||<3000,3,if(||RWS||<5000,4,if(||RWS||<6000,5,if(||RWS||<7000,6,if(||RWS||<8000,7,if(||RWS||<9000,8,if(||RWS||<10000,9,if(||RWS||<11000,10,if(||RWS||>=11000,10)))))))))))) (the || are closed]] and open [[ brackets in the actual formula, but Moodle doesn't seem to like them in the forum!) I get an "Invalid formula" message and can't save it.
Still not sure what I am doing wrong...
If you have any ideas I would be most grateful. Thank you again.
In reply to Eric Hagley

Using if in calculation formula - not right yet

by alberto ro -
"invalid formula" usually means "parentesis missing", "coma missing" or any syntax error
Well, try bit a bit
if(RWS==0, 0, if(RWS<=500,1,2))

with that formula I think you could check the "if" clause.
On the other hand, when I talked about "range" I was thinking that calculation was wrong. If one single quizz can reach 500 but the maximum quizz grade is set to 100,then I just have 100.
In reply to alberto ro

Using if in calculation formula - not right yet

by Eric Hagley -
Picture of Particularly helpful Moodlers
Thank you again Alberto,
Even =if([RWS]=0, 0, if([RWS]<=500,1,2)) gives me invalid formula so I am not sure what is wrong here. (I tried it without the = at the start but it said "formula must start with an =)
In reply to Eric Hagley

Using if in calculation formula - not right yet

by alberto ro -

You need double brackets [ [  ;   ] ]

and compare equal is double equal "=="

formula need "=" before.

Check if RWS category returns a number

In reply to alberto ro

Using if in calculation formula - not right yet

by alberto ro -

I hope you could see the image.

The formula that I use is on the right ( I have to use ";" instead "," because my mother tongue is Spanish).

It looks the calculation works.

In reply to alberto ro

Using if in calculation formula - not right yet

by Eric Hagley -
Picture of Particularly helpful Moodlers
Thank you again Alberto
This is what I have but I still get invalid formula
Still not working unfortunately...
Attachment Screenshot 2025-11-21 at 3.14.35 pm.png
Attachment Screenshot 2025-11-21 at 3.29.29 pm.png
In reply to Eric Hagley

Using if in calculation formula - not right yet

by alberto ro -
Could your "Reading" category hasn't any "id"?
In your snapshot it looks that.
Check it in "configuring qualifications"
category id

In reply to alberto ro

Using if in calculation formula - not right yet

by Eric Hagley -
Picture of Particularly helpful Moodlers
This is how it is has always been set Alberto and again, I am very grateful that you are continuing to follow this. Thank you. (and with RWC it is still the same.)
Attachment Screenshot 2025-11-21 at 10.06.11 pm.png
Attachment Screenshot 2025-11-21 at 9.57.50 pm.png
In reply to Eric Hagley

Using if in calculation formula - not right yet

by alberto ro -

But your id is "RWC" whereas your formula tries to use "RWS"

In reply to alberto ro

Using if in calculation formula - not right yet

by Eric Hagley -
Picture of Particularly helpful Moodlers
Sorry, I did have RWC in there. Still was the same result.
In reply to Eric Hagley

Using if in calculation formula - not right yet

by Eric Hagley -
Picture of Particularly helpful Moodlers
Something very strange I have just noticed (or maybe it is not strange at all). I had thought I had to put the equation in where it says "Aggregation Reading Word Count" but if I put it in there it doesn't work. If I put it in the Aggregation Reading Word Count 2 section, which is above section 1 using the ID for section 1, it seems to work. Is that what it is meant to do?
Actually if I put
=if(RWC==0,0,if(RWC<500,1,if(RWC<1000,2,if(RWC<3000,3,if(RWC<5000,4,if(RWC<6000,5,if(RWC<7000,6,if(RWC<8000,7,if(RWC<9000,8,if(RWC<10000,9,10)))))))))) 
in any of the aggregation sections except the RWC one, it works. If I put it in the RWC one it says invalid formula. That seems very strange to me.
In reply to Eric Hagley

Using if in calculation formula - not right yet

by alberto ro -

It has sense. You,.. or I, have said that total of a category is "natural" and in the other hand, we command that moodle has to use a formula instead "natural"

Then, solution is clear. Let category as natural. Set a new item with formula (out of the category)

If you don't want to missunderstand your student, I suposse you could hide "total of category"

In my previous image you can see that check item is out of  category

In reply to alberto ro

Using if in calculation formula - not right yet

by Eric Hagley -
Picture of Particularly helpful Moodlers
Thank you for helping me work my way around this. I think I now have a solution similar to what you have stated here. Just have categories for the formula and have the categories that have the actual quizzes / assessment items in them have no actual grade. Kind of strange but you have to do what you have to do.