Online Only 365 Excel (Restricted)

[cknader]

I am attempting to create a tracker tool and am rusty in excel. However coming bad to corporately restricted excel and given a project that needs excel's full capabilities.

• No access to developer tab
• No VBA
• Online version only

I am to create a simple tracker that reviews and scores a contractor based on tasks performed during the day.

Bob takes a photo of the tires but forget the in cab photo. Scores out and averages over stops performed.

I would like a checkbox that would score 100 if they if click it over the calculated average if true.

I think this should work but does not: =IF(J2=TRUE,100,=AVERAGE(K2:K10)) What am I missing?

 

[Shortmeister1]

Can you be more specific please. A checkbox cannot have a value of 100, just TRUE or FALSE. It would also be helpful to the casual viewer if you included the row and column references in the graphic.

There's also a syntax error in your IF statement. You can't have an equals sign as an output.

I suspect that you want to do something like this maybe?

=IF(AND(I2=TRUE,AVERAGE(J2:J10)=100),TRUE,FALSE)

And your column I would presumably have an AND condition that would show as TRUE if the preceding 3 checkboxes are all ticked?

 

[cknader]

I am assigning a value to a cell if the checkbox is true
=IF(C2:I2=TRUE,14.29,0)

This is then fed to another cell
=SUM(L2#)

The end score is an average of the rows scored.
=AVERAGE(J2:J13)

However without VBA, I do not know how to create a check all checkbox. So the idea is to use a logic statement to swap.

If this checkboxALL is check value is 100 else use the average.

I hope that makes more sense.

 

[cknader]

Please remember I am using a corporate online only version of 365 excel with no developer tab or vba ability.

 

[Scott Huish]

Can you be more specific please. A checkbox cannot have a value of 100, just TRUE or FALSE. It would also be helpful to the casual viewer if you included the row and column references in the graphic.

There's also a syntax error in your IF statement. You can't have an equals sign as an output.

I suspect that you want to do something like this maybe?

=IF(AND(I2=TRUE,AVERAGE(J2:J10)=100),TRUE,FALSE)

And your column I would presumably have an AND condition that would show as TRUE if the preceding 3 checkboxes are all ticked?

This may not solve the op's problem but the IF is not necessary, AND returns TRUE/FALSE on its own.
=AND(I2,AVERAGE(J2:J10)=100)

 

[FormR]

I think this should work but does not: =IF(J2=TRUE,100,=AVERAGE(K2:K10)) What am I missing?

Hi, you don't need that third "=" sign. The =TRUE part is superfluous, but not necessarily a problem. So either.

=IF(J2=TRUE,100,AVERAGE(K2:K10))

Or just.

=IF(J2,100,AVERAGE(K2:K10))