NEED HELP! T_T - Automatic update value when new data is filled at adjacent column

guys i need help!

Let say i have target value at column C2 which is 1.18.
Then from column D1 to O1 is months from Aug 14 until Jul 15.
So i have column Q2 which is my current month result which is pass or fail (need to put smiley face - happy or sad) when compared to my target value.

So from column D2 to O2 is value for each month. Let say currently, i only have value for D2 (aug 14) which is 1.2 - this will give happy face since it is above the target when compared with column C2.

But, if i have new value to input which is in E2 (sep 14) let say 0.8, how my result in column Q2 will update the result based on new data input on adjacent column - which the result will be sad face since its below the target (automatic update).

Thank you

Example.xlsx

This is the attachment example

Thank you.
I have included the attachment file

Anuyone can help me? im stuck..i dont know how the result can be updated when new value is input in adjacent column...

Try this formula in Q3:

Formula:

Please Login or Register  to view this content.

Does this do what you want?

Originally Posted by FlameRetired

Try this formula in Q3:

Formula:

Please Login or Register  to view this content.

Does this do what you want?

Thank you. what if let say i want to put flat face if the value is 90% meet the target? can you help me? so there are additional condition for example.

Thanks

HAPPY FACE.xlsx

Hi, have a look at this example. I added data in an extra row (row 3) and then completed the required task. If you didn't want to see the data in row 3 you could always hide this row.

Cheers

Well done FlameRetired - you cam up with a good solution while I was working on it!

Originally Posted by Ursul

Well done FlameRetired - you cam up with a good solution while I was working on it!

Thanks for the feedback.

Originally Posted by FDibbins

Try this...
=IF(INDEX($D$3:$O$3,MATCH(99^99,$D$3:$O$3))=C3,"J",IF(OR(INDEX($D$3:$O$3,MATCH(99^99,$D$3:$O$3))>=C3*0.9,INDEX($D$3:$O$3,MATCH(99^99,$D$3:$O$3))<=C3*1.1),"K","L"))

This assumes that if you exactly match goal, you get smiley, if you get +90 -110, you get straight else you get frown

yess! thanks alot!! IT REALLY HELPS ME. THANK YOU SO MUCH!!

Thanks for the rep!

Originally Posted by FDibbins

Happy to help, thanks for the feedback

No worries.
By the way, do you have any idea how I can colour the smilye face? Happy-green straight -yellow frown - red..

I tried to use conditional formatting and the smiley face was coloured (only outline -eyes, mouth and circle was green but the face is white background). The thing that i want to get is the smiley face has black color for eyes, mouth and circle outline but fill with green colour for face. Do you get what i meant? is it possible?

Thanks

I tried fooling around with copy and paste as picture.......got some interesting effects but nothing like you described......it also stops responding to the formula. You will probably have to do some lengthy research on this one if it can be done at all.

Originally Posted by FlameRetired

I tried fooling around with copy and paste as picture.......got some interesting effects but nothing like you described......it also stops responding to the formula. You will probably have to do some lengthy research on this one if it can be done at all.

Yea same with me. btw its ok. thanks!

Originally Posted by FDibbins

Yes, I was not able to get what you wanted either TRied inserting special characters, but that didnt work either

yea me too. i tried but still cant get it. btw thanks! =D