• 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.

calc. the cell value based on cell color (with if condition)

G

ganeshm

Member
dear experts,

i have calculated cell value based on cell color. How can i combine both the formula in one cell.
Since the formula differs for both the cell colors. I would like the result to be displayed in positive value.

Also, if the cell color changes blue, the corresponding formula need to work.
and if the cell color changes to green, the corresponding formula need to work


Regards,
ganeshm
 

Attachments

  • if Condition with cell color.xlsx
    12.1 KB · Views: 9
Hi,

I am not an expert in this but I have tried it before with no glory. I think even the VBA does not recognize the displayed color if it is a conditional formatting. It will have to workout the conditional formatting too to get the displayed color.

Since the condions in the color is simple, you can integrate the same condition when writing the formula. Of course the formula will be lengthy and ugly.

In the below file, I just combined your 2 different formulas and put an additional if equivalent to the conditional formatting. This might work for you.

@experts: Is there a way to work this out? I had hard time figuring out the displayed color of the cell through VBA which is under numerous conditional formatting. If anyone has a solution. Please post.
 

Attachments

  • if Condition with cell color.xlsx
    12.9 KB · Views: 9
Hi, ganeshm!

I didn't fully understand your issue, but this is my blind shot:
a) Excel doesn't do anything automatically within formulas, regarding cell colors, so everything should be down by CF or by macros
b) In your case for F2:F3 cells, if you want to show them in positive you have 2 options: embrace the formulas into an external ABS function, or use a format without sign for negative numbers; however in both cases the stored value will be different, with positive sign in the 1st choice and with positive/negative in the 2nd.

Regards!
 
Thank you BBD & Sir JB7.

I at least learned some new function.
Also, i just followed positive sign 1st and then negative as suggested by SirJB7.
Finally, my work is minimized. Thank you again for the suggestion.
 
further to the above post the following formula works well,

=IF(D2>0,LOOKUP(A2,{49,50,60,75,90},{0,2.5,5,7,10}%*B2),LOOKUP(A2,{49,50,60,75,90},{0,25,50,75,100}%*D2))

and also ABS function. BBD's idea and SirJB7's input was very useful.
 
Hi, ganeshm!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top