# Calculating the "most" used text answer or average of all text-multiple choice answers

• 38
• Question
• Updated 8 months ago
• In Progress
I have child "Task" records that each have a status assigned to the record. I want to calculate the overall status of the Parent "Deliverable" to give an idea of where all the sub tasks level in an overall status.

For example out of 5 tasks 2 are yellow, 1 is red and 3 are green, so the overall status of the deliverable to describe the tasks would equal GREEN.
• 312 Points

Posted 9 months ago

• 38
• 75,144 Points
np
You would make a summary field on the Parent to total up the # of Greens, # of Yellows and # of red, in three separate fields.

Then make a formula to calculate the average Task Status.

Post back if you need help with the formula.
• 312 Points
Hi QuickBase Coach,

Could you please provide me with the formula as well? I will work on the summary fields first.

Thanks,
Aylin
• 312 Points
I actually think I did the Summary field incorrectly because of what I choose for the "Total of" part of the summary field. I think it's wrong because the total of the records on the screen shot says 6 and not 3. Please advise.

• 75,144 Points
The summary field should just be a Count if the number of records. Nothing to do with the Record ID.
• 75,144 Points
Let me know when you have the summary fields working.
• 312 Points
I fixed the summary fields! I didn't change the first section to a specific field, that's where I messed up.
• 75,144 Points
Actually, here isa format for a formula

var number Max = Max([# green], [# yellow], [#red])

Case(\$Max,
[#green], "Green",
[#yellow], "Yellow",
[#red], "Red")
• 312 Points
Hi,

I have just a couple questions on this:

1. Is this all one formula?
2. What does the "var number" mean above?
3. #green, does this need to be the #FieldNameofTotalGreen?
4. Field Type is: Formula - Rich Text, correct?

Thanks!
Aylin
• 75,144 Points
1. Yes this is all one formula.

2. The var refers to a formula variable.  it is a useful technique to make formulas more readable.  Here is some help text.  https://help.quickbase.com/user-assistance/formula_variables.html

3. Yes, you would put in the name of your field for the total # of green.

4. The formula is formula text , not formula rich test.
• 312 Points
Hi I'm almost there!
1. I'm getting this error message for the first "MAX" shown below. What does MAX refer to?

2. Just to take this one step ahead while we are talking about it. I will also like to average the formula of the Average Task Statuses to the related parent record, would I make a formula-text field on the parent table for the Averaged child records of the Grand child records?
• 75,144 Points
Please post your code too as I cannot correct a screen shot.  It looks like the formula variable is missing the last semi colon.

We might have to rename the formula variable to call it "highest". Maybe Max is a reserved word.

The formula is using a formula variable just to increase the readability of the formula. https://help.quickbase.com/user-assistance/formula_variables.html

As for your second question, I  am not understanding your question.  Can you explain with an example.

• 312 Points
• 312 Points

var Number Max = Max([Total Green Workstream Status], [Total Yellow Workstream Status], [Total Red Workstream Status])

Case(\$Max,

[Total Green Workstream Status], "Green",

[Total Yellow Workstream Status], "Yellow",

[Total Red Workstream Status], "Red")
• 75,144 Points
It needs a semi colin on his first line

var Number Max = Max([Total Green Workstream Status], [Total Yellow Workstream Status], [Total Red Workstream Status]);
• 312 Points
Yay that worked! Also, how would I change the formula to keep the field blank if there are no related Workstream Status Records?
• 75,144 Points
Make this change to blank the result if all zero.

IF(\$Max > 0,

Case(\$Max,

[Total Green Workstream Status], "Green",

[Total Yellow Workstream Status], "Yellow",

[Total Red Workstream Status], "Red")

If([PicExistsFull], "<a target='_blank' href=" & \$URL & ">" & \$Words & "</a>", " ")
)

be sure that the field properties of the the three Totals fields are set to treat blank as zero.
• 312 Points

• 75,144 Points
OK, now I see your relationship but you will need to explain this with an example

I will also like to average the formula of the Average Task Statuses to the related parent record, would I make a formula-text field on the parent table for the Averaged child records of the Grand child records?