Formatting Discrepancy

c772301

New Member
Joined
Apr 30, 2022
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
I cannot understand the -red- font color format in cells U4 and U5.
Mini sheet pasted below.
Any help much appreciated!!



RUBY $$$.xlsx
ABCDEFGHIJKLMNOPQRSTU
1C-----A-----T-----E-----R-----I-----N-----G
2DateIn OutTipsDayShiftHrsHourlyTotal$/HrDateInOutGratHrsHourlyAutoTotal$/Hr
319-Feb11:30 AM3:45 PM$143SaturdayLunch4:15$30$173$4129-Mar9:15 AM12:00 PM$02:45$28$20$48$17
419-Feb3:45 PM10:05 PM$122SaturdayDinner6:20$44$166$2614-Apr10:00 AM11:55 AM$191:55$19$20$58$30
520-Feb11:30 AM5:45 PM$119SundayLunch6:15$44$163$2619-Apr10:15 AM11:40 AM$51:25$14$20$39$28
622-Feb3:45 PM10:00 PM$93TuesdayDinner6:15$44$137$2226-Apr10:45 AM12:15 PM$191:30$15$20$54$36
7$638$29
824-Feb5:15 PM8:15 PM$50ThursdayDinner3:00$21$71$24     
925-Feb3:45 PM9:45 PM$110FridayDinner6:00$42$152$25     
1026-Feb4:30 PM8:50 PM$47SaturdayDinner4:20$30$77$18     
1127-Feb3:45 PM10:00 PM$85SundayDinner6:15$44$129$21     
121-Mar11:45 AM5:15 PM$113TuesdayLunch5:30$38$151$28     
131-Mar5:15 PM7:00 PM$22TuesdayDinner1:45$12$34$20     
14$614$22
153-Mar4:45 PM9:00 PM$50ThursdayDinner4:15$30$80$19     
166-Mar11:45 AM6:30 PM$113SundayLunch6:45$47$160$24     
176-Mar6:30 PM7:00 PM$7SundayDinner0:30$3$10$21     
18$250$21
199-Mar4:30 PM6:45 PM$28WednesdayDinner2:15$16$44$19     
2011-Mar11:15 AM4:00 PM$72FridayLunch4:45$33$105$22     
2112-Mar11:45 AM2:00 PM$42SaturdayLunch2:15$16$58$26     
2213-Mar11:00 AM3:20 PM$98SundayLunch4:20$30$128$30     
2313-Mar4:45 PM7:40 PM$46SundayDinner2:55$20$66$23     
2415-Mar11:30 AM5:45 PM$91TuesdayLunch6:15$44$135$22     
2515-Mar6:00 PM6:45 PM$4TuesdayDinner0:45$5$9$12     
26$545$22
2717-Mar4:30 PM8:15 PM$56ThursdayDinner3:45$26$82$22     
2818-Mar3:45 PM8:45 PM$75FridayDinner5:00$35$110$22     
2922-Mar11:00 AM5:15 PM$94TuesdayLunch6:15$44$138$22     
3022-Mar5:15 PM7:45 PM$30TuesdayDinner2:30$17$47$19     
31$377$21
Sheet1
Cell Formulas
RangeFormula
F27:F30,F19:F25,F15:F17,F8:F13,F3:F6F3=IF(B3="","",TEXT(B3,"dddd"))
G27:G30,G19:G25,G15:G17,G8:G13,G3:G6G3=IF(B3="","",IF(C3<TIME(12,0,0),"Lunch",IF(C3>=TIME(12,0,0),"Dinner","0")))
H27:H30,H19:H25,H15:H17,H8:H13,H3:H6H3=IF(B3="","",D3-C3)
I27:I30,I19:I25,I15:I17,I8:I13,I3:I6I3=IF(B3="","",6.98*(H3*24))
J27:J30,J19:J25,J15:J17,J8:J13,J3:J6J3=IF(B3="","",E3+I3)
K27:K30,K19:K25,K15:K17,K8:K13,K3:K6K3=IF(B3="","",J3/(H3*24))
J7,J31J7=SUM(J3:J6)
K7,K31K7=AVERAGE(K3:K6)
J14J14=SUM(J8:J13)
K14K14=AVERAGE(K8:K13)
J18J18=SUM(J15:J17)
K18K18=AVERAGE(K15:K17)
J26J26=SUM(J19:J25)
K26K26=AVERAGE(K19:K25)
Q27:Q30,Q19:Q25,Q15:Q17,Q8:Q13,Q3:Q6Q3=IF(M3="","",O3-N3)
R27:R30,R19:R25,R15:R17,R8:R13,R3:R6R3=IF(M3="","",10*(Q3*24))
S27:S30,S19:S25,S15:S17,S8:S13,S3:S6S3=IF(M3="","",20)
T27:T30,T19:T25,T15:T17,T8:T13,T3:T6T3=IF(M3="","",(P3+R3+S3))
U27:U30,U19:U25,U15:U17,U8:U13,U3:U6U3=IF(M3="","",T3/(Q3*24))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U4:U6Cell Valuebelow averagetextNO
U3Cell Valuebelow averagetextNO
K3Cell Valuebelow averagetextNO
U7:U241Cell Valuebelow averagetextNO
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hello,

the Conditional formating rule for the U4:U6 is below the average,
the average of
30
28
36
Average= 31.33 so the 30 and 28 are below average this is why they have red font.
 
Upvote 0
Thank you saeedkk!! I don't know how that conditional formatting got there but it was easy to remove it. Appreciate the help!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top