JAYLEECAKE

New Member
Joined
Feb 8, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
I am losing my mind over this. I am very well acquainted with Excel; however, this super basic formula is killing me. Have I completely lost it?!

We get a task on certain dates (DATE) and depending on the task, it may either have a 75-day suspense or 180-day suspense (STANDARD).

To find out the number of days the tasker has been active, I calculated the DAYS column with [=DAYS(TODAY(),$A2)]. Works great.

To find out how overdue something is, I calculated the "percent" (though not *100) with [=$C2/$B2]. Works great.

PROBLEM! When I use conditional formatting to highlight in red the cells in PERCENT that are greater than 1 [=$D2>1], it goes wild and highlights Row 2 as well even though $D$2=2.43.

(I will also be adding that if it's greater than or equal to .73 but less than 1 it should be YELLOW. Otherwise, GREEN.)

I have tried this on multiple computers, on brand new spreadsheets, etc. I have tried enclosed IF functions in a separate column to give a RED, GREEN, YELLOW answer, then base the conditional formatting off of that. IT STILL HIGHLIGHTS INCORRECT ROWS! I have no idea what is going on. I feel like I am losing my mind over here...

PLEASE HELP!

Formats: DATE=Date, STANDARD, DAYS, PERCENT all = Number
 

Attachments

  • ExcelIssue.jpg
    ExcelIssue.jpg
    147.2 KB · Views: 16

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,916
Office Version
  1. 2016
Platform
  1. Windows
Hi,

The number 1 (one) in percentage terms is 100%, so you want the percentage equivalent of the number 1, you'll need to use 0.01
.73 is actually 73%, etc.
 

JAYLEECAKE

New Member
Joined
Feb 8, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hey there, I didn't actually turn them into percentages. They are still just the decimal (that's why I said I didn't multiply by 100). If you look at the attached screenshot, you'll see what I mean.

Thanks! - J
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
You need to change D2 to D1 in the CondFrmt formula because you've selected the entire C column.
 

JAYLEECAKE

New Member
Joined
Feb 8, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

OH. MY. Wow. Thank you! If I turn it into a table (with headers), would I still need to use D1 instead of D2?
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
Just turn into a Table, select the Days column and create the formula with =D2>1. When you add or remove Table Rows, the CondFrmt rule will adjust.
 
Solution

JAYLEECAKE

New Member
Joined
Feb 8, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You rock! Thank you!
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
I blush. You're welcome,

By the way, this video from ExcelIsFun on the youtube really lays it all out.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,916
Office Version
  1. 2016
Platform
  1. Windows
Hey there, I didn't actually turn them into percentages. They are still just the decimal (that's why I said I didn't multiply by 100). If you look at the attached screenshot, you'll see what I mean.

Thanks! - J

Oops, missed that part, glad you got sorted out now.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,186
Members
416,077
Latest member
SJSB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top