Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Need a formula to highlight cell

Copper Contributor

Hi,


I need some help in creating a formula.

 

Example:

 

H4 contains the value 10%

G5 contains the value 50

D5 contains the value 42

 

H5 is where the formula goes:

 

When G5 is less than H4 and is less than D5 then highlight the cell

 

I have attempted to create the formula but got stuck on how to incorporate D5

 

=$G5*(1-H$4)

 

Thanks for any help!

Patrick

 

6 Replies

@packie 

What exactly do you mean by "G5 is less than H4"? G5 is a number and H4 is a percentage...

At the moment the formula =$G5*(1-H$4) will return 45
so therefore 50 (G5) less 10% (H4) will return 45

So what I need to know is 45 (H5) equal or more than 42 (D5) and in this example it is more so therefore highlight cell H5

@packie 

 

That is different from your original description! Use this formula in the Conditional Formatting rule of type 'Use a formula to determine which cells to format':

 

=$G5*(1-H$4)>=D5

I'm afraid I couldn't get it to work.

Perhaps this sample sheet will help....

https://1drv.ms/x/s!Ai3wejactZJ_licmHMLM2tBCbH7G?e=lUGu3h
best response confirmed by packie (Copper Contributor)
Solution

@packie 

I have edited your workbook.

Made a small change, will work for me.
Thanks :)
1 best response

Accepted Solutions
best response confirmed by packie (Copper Contributor)
Solution

@packie 

I have edited your workbook.

View solution in original post