Question description:
This user has given permission to use the problem statement for this
blog.
I need an IF formula where, IF there is a manual adjustment in a whole column of 100% values (say someone changes one to 90%) would say "Yes" or "No". Tried to solve this with =IF(AVERAGE(...)=100%, "No", "Yes") but it is kind of sloppy).
Solved by M. H. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
07/08/2018 - 10:31
Hello RG
Excelchat Expert
07/08/2018 - 10:32
Hello, I understand that you need help in checking if one value is changed from 100% to something else, you'll get a message that says Yes or NO, right?
User
07/08/2018 - 10:32
Exactly
Excelchat Expert
07/08/2018 - 10:33
Can you show me an example please?
User
07/08/2018 - 10:33
i did on the sheet on the right
User
07/08/2018 - 10:33
a column of 100%'s
User
07/08/2018 - 10:33
and a cell for the formula
Excelchat Expert
07/08/2018 - 10:33
Can you show me an example of when one is changed to something else and the output you expect?
User
07/08/2018 - 10:34
So I changed one to 10% and the result is "Yes"
User
07/08/2018 - 10:34
But if I change another to 190% then the result would be "No" as the average value is 100% again
Excelchat Expert
07/08/2018 - 10:34
I can see that you have a formula in place, may I know why this doesn't work?
User
07/08/2018 - 10:35
because now I have 2 values changed but the average is the same
User
07/08/2018 - 10:35
therefore the "No" is actually wrong
Excelchat Expert
07/08/2018 - 10:35
Oh, I see. Yes you are right.
User
07/08/2018 - 10:35
So need something else
Excelchat Expert
07/08/2018 - 10:35
Alright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User
07/08/2018 - 10:35
maybe a formula that checks all values in all cells?
User
07/08/2018 - 10:35
yes sure
Excelchat Expert
07/08/2018 - 10:36
That's what I have in mind.
Excelchat Expert
07/08/2018 - 10:36
A simple countifs that checks for non 100%.
Excelchat Expert
07/08/2018 - 10:37
Please help me out by testing it.
Excelchat Expert
07/08/2018 - 10:37
Formula is in J6.
User
07/08/2018 - 10:38
Can't believe I forgot about countifs
User
07/08/2018 - 10:38
thank you so much!
Excelchat Expert
07/08/2018 - 10:38
Glad to have helped!
Excelchat Expert
07/08/2018 - 10:39
Would there be anything else that I can help you with regards to the original question?
User
07/08/2018 - 10:39
that's all thank you
User
07/08/2018 - 10:39
5 stars from me
User
07/08/2018 - 10:39
have a good day
Excelchat Expert
07/08/2018 - 10:39
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
07/08/2018 - 10:39
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
07/08/2018 - 10:39
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
Excelchat Expert
07/08/2018 - 10:44
Still with me?
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
