SOLVED

Conditional Formatting based on two cells, output in a third.

%3CLINGO-SUB%20id%3D%22lingo-sub-3326406%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20based%20on%20two%20cells%2C%20output%20in%20a%20third.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3326406%22%20slang%3D%22en-US%22%3E%3CP%3EMaybe%20conditional%20formatting%20won't%20help%20at%20all.%20Please%20don't%20mind%20the%20English%20as%20I%20have%20translated.%20Also%20this%20is%20what%20was%20given%20me%20by%20my%20MIL%20to%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20have%20this%20grading%20sheet%20where%20I%20have%20two%20grades%3CSTRONG%3E%20(B%20and%20D)%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20rules%20is%20basically%3C%2FP%3E%3CP%3E%3CSTRONG%3Eif%20B%20%3D%26gt%3B70%20--%20approved%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Eif%20B%20%3D%26lt%3B70%20--%20failed%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20output%20is%20on%20C.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESame%20with%20column%20D%2C%20output%20is%20on%20E.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIgnoring%20F%2C%20%3CSTRONG%3EI%20need%20help%20with%20column%20G.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image_2022-05-05_133720763.png%22%20style%3D%22width%3A%20810px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369527i63E71AC47AC83DE7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image_2022-05-05_133720763.png%22%20alt%3D%22image_2022-05-05_133720763.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EG%20is%20for%20Final%20aprove%20or%20Fail.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ETo%20be%20approved%2C%20both%20B%20and%20D%20have%20to%20have%20a%20value%20of%20%3D%26gt%3B70.%20If%20even%20just%20one%20is%20less%20than%2070%2C%20it's%20considered%20a%20fail.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20basically%20have%20200%20rows%20to%20take%20care%20of%2C%20I%20tried%20conditional%20formatting%20by%20creating%204%20rules%20but%20basically%20they%20cancelled%20themselves%20out.%20And%20even%20then%2C%20I%20could%20not%20also%20make%20the%20output%20to%20be%20APPROVED%20or%20FAIL.%20Remembered%20is%20only%20cell%20format%2C%20but%20even%20then.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formulas%20I%20tried%20on%20column%20G%3C%2FP%3E%3CP%3E%3D%24B3%3D%26gt%3B70%20--%20green%3CBR%20%2F%3E%3D%24B3%3D%26lt%3B70%26nbsp%3B--%20red%3C%2FP%3E%3CP%3E%3D%24D3%3D%26gt%3B70%26nbsp%3B--%20green%3CBR%20%2F%3E%3D%243D%3D%26lt%3B70%26nbsp%3B--%20red%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E--%3C%2FP%3E%3CP%3EApreciate%20any%20help%2C%20m%20y%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3326406%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3326613%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%2C%20output%20in%20a%20third.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3326613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1382191%22%20target%3D%22_blank%22%3E%40anacrisp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20as%20shown%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3326986%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%2C%20output%20in%20a%20third.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3326986%22%20slang%3D%22en-US%22%3Ethank%20you%20so%20much%20for%20the%20help!%3CBR%20%2F%3EIf%20i%20may%20ask%20for%20a%20little%20more%20help..%3CBR%20%2F%3EIf%20the%20person%20did%20not%20take%20the%20test%2C%20they%20are%20mark%20absent%20on%20the%20grade%20column%20(B%20and%20D%20columns)%2C%20therefore%20as%20ELIMINATED%20on%20the%20Final%20(G%20column)%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20could%20I%20add%20to%20the%20formula%2C%20if%20possible%3F%3CBR%20%2F%3EThank%20you%20so%20much%20once%20again!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3327224%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%2C%20output%20in%20a%20third.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3327224%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1382191%22%20target%3D%22_blank%22%3E%40anacrisp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20welcome.%20You%20can%20try%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Maybe conditional formatting won't help at all. Please don't mind the English as I have translated. Also this is what was given me by my MIL to help. 

 

So I have this grading sheet where I have two grades (B and D)

The rules is basically

if B =>70 -- approved
if B =<70 -- failed

The output is on C.

 

Same with column D, output is on E. 

 

Ignoring F, I need help with column G.

image_2022-05-05_133720763.png

G is for Final aprove or Fail. 

To be approved, both B and D have to have a value of =>70. If even just one is less than 70, it's considered a fail.

 

I basically have 200 rows to take care of, I tried conditional formatting by creating 4 rules but basically they cancelled themselves out. And even then, I could not also make the output to be APPROVED or FAIL. Remembered is only cell format, but even then. 

The formulas I tried on column G

=$B3=>70 -- green
=$B3=<70 -- red

=$D3=>70 -- green
=$3D=<70 -- red

 

--

Apreciate any help, m y

3 Replies

@anacrisp 

Maybe as shown in the attached file.

thank you so much for the help!
If i may ask for a little more help..
If the person did not take the test, they are mark absent on the grade column (B and D columns), therefore as ELIMINATED on the Final (G column)

How could I add to the formula, if possible?
Thank you so much once again!
best response confirmed by anacrisp (New Contributor)
Solution

@anacrisp 

You are welcome. You can try the attached file.