• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting average of the No/Partially/Yes in color in once cell ?!!!

K

Kanagat

New Member
Good morning, Excel experts!

I have question, i was trying to create some kind of checklist. I've attached the file so you can get familiar with...
Basically it should calculate average of the score where the inputs will be Yes/partially/No
and as the result of that average it should color one of the cell to green/amber or red

Thank you guys in advance....
 

Attachments

Hi ,

An average , as most of us understand it , is possible only with numeric values ; can you explain how values such as N , P and Y should be averaged ?

Narayan
 
NARAYANK991 said:
Hi ,

An average , as most of us understand it , is possible only with numeric values ; can you explain how values such as N , P and Y should be averaged ?

Narayan
Click to expand...

Maybe... to assign number to N=0, P=1, Y=2
let's say there will be 4 items that should be filled so we have maximum 8 and min 0
 
Hi Kanagat,

Alternatevily, if you place value 0,1,2 in B26:B28 and use below formula in C6:

=AVERAGE(LOOKUP($Q15:$Q20,$C$26:$C$28,$B$26:$B$28))

Note this is an array formula, so must be entered with Ctrl+Shift+Enter.

Regards,
 
Hi ,

I think there is some misunderstanding of what the problem is :

1. You want the average of some values such as N , P , Y which will be entered in the range Q15 through Q20.

You have clarified that instead of N , P and Y , we can assign the values 2 , 3 and 4 instead ; did you mean that the data entry would be N , P and Y still , or would the data entry be 2 , 3 and 4 ?

2. You want the cell colours to change based on whether the average is shall we say low , medium or high ; the cell colouring can be achieved by conditional formatting , and is not a problem.

The main problem is averaging text values ; if you insist that the data entry will be N , P and Y , then we will have to have a lookup table to convert these text values to numbers such as 2 , 3 and 4.

Narayan
 
NARAYANK991 said:
Hi ,

I think there is some misunderstanding of what the problem is :

1. You want the average of some values such as N , P , Y which will be entered in the range Q15 through Q20.

You have clarified that instead of N , P and Y , we can assign the values 2 , 3 and 4 instead ; did you mean that the data entry would be N , P and Y still , or would the data entry be 2 , 3 and 4 ?

2. You want the cell colours to change based on whether the average is shall we say low , medium or high ; the cell colouring can be achieved by conditional formatting , and is not a problem.

The main problem is averaging text values ; if you insist that the data entry will be N , P and Y , then we will have to have a lookup table to convert these text values to numbers such as 2 , 3 and 4.

Narayan
Click to expand...


Narayan,

Data entry will be N/P/Y (for Q15-Q20) I've attached the file again, and it will change the color of C6 according to results )))
 

Attachments

You must log in or register to reply here.
Back
Top