Conditional formula

ads_1471

Board Regular
Joined
Apr 19, 2016
Messages
58
Hi after a formula for conditional formatting

In column A I have dropdowns where I select an item.
Col B.C,D are amounts
Col E is a final amount

I want it so in Col E the formula is if Col A sees a word then when Col E is greater than 80 it will be green.

If that makes sense :)
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi ads_1471,

Does this do what you want?

ADS_1471.xlsx
ABCDE
1Text1Val1Val2Val3Total
2A3263059
3B31252581
4C2991149
5D226625113
6E20323183
7255566146
8F1223356
9G22177100
10107787
11H30142064
Sheet1
Cell Formulas
RangeFormula
E2:E11E2=SUM(B2:D2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E18Expression=AND(A2<>"",$E2>80)textNO
 
Upvote 0
Solution
Hi ads_1471,

Does this do what you want?

ADS_1471.xlsx
ABCDE
1Text1Val1Val2Val3Total
2A3263059
3B31252581
4C2991149
5D226625113
6E20323183
7255566146
8F1223356
9G22177100
10107787
11H30142064
Sheet1
Cell Formulas
RangeFormula
E2:E11E2=SUM(B2:D2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E18Expression=AND(A2<>"",$E2>80)textNO
Yes thanks. Brilliant.

How do you all learn all these formulas :( I can do basic but struggle with them.
 
Upvote 0
You're welcome.

If you click Formulas you'll see them all listed by category. You can use the Microsoft site for more details: Excel Functions

The tricky bit with Conditional Format is the debugging as you don't see if it has returned TRUE or FALSE until the OK so I use a work column which you can see if I show more of the sheet.

ADS_1471.xlsx
ABCDEFG
1Text1Val1Val2Val3Total
2A3263059FALSE
3B31252581TRUE
4C2991149FALSE
5D226625113TRUE
6E20323183TRUE
7255566146FALSE
8F1223356FALSE
9G22177100TRUE
10107787FALSE
11H30142064FALSE
12FALSE
Sheet1
Cell Formulas
RangeFormula
E2:E11E2=SUM(B2:D2)
G2:G12G2=AND(A2<>"",$E2>80)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E18Expression=AND(A2<>"",$E2>80)textNO
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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