excel4u
Registered User

Conditional Formatting - Using IF, AND, OR together.

Hi, I'm, hoping you can help me with a query.

I have a number of columns as follows:

Column A: Drop down (list) with selections of Y, N or na

Column B: Formula to calculate A into a score, so as Y=5, N=0 and na=blank (as follows):
=IF(A1="Y",5,IF(A1="N",0,IF(A1="na","",)))

Column C: Drop down (list) with selections of either Y or N

Column D: Formula to calculate C into a score, so as Y=5, N=0 (as follows):
=IF(C1="Y",5,IF(C1="N",0,))

BUT – I am wanting COLUMN D formula to include option (OR or AND) where the following additional rule can apply:
IF C1 = “Y” and A1 = “na” …. D1 = 10

i.e. I so all the following scenarios are possible
- A=Y / C=Y 10
- A=Y / C=N 5
- A=N / C=Y 5
- A=N / C=N 0
- A=na / C=N 0
- A=na / C=Y 10

It's the last option where C=Y but it can be either worth 5 or 10 (if A=”na”) where my problems are & seek help with my formula.

Your help is appreciated. Thanks.

dear freinds i am a hvac estimator and using one boq templets to make boq,when i put required data to boq templets its gives complete boq in excel but some rows without quantity,these rows i want to hide in one shot,manually we can hide but it takes time,any can help me how i can hide these empty rows in one shot.write me at ehank2009@gmail.com thanks

I have 3 Columns A1 B1 C1
A1 being a variable column.
B1 has value say 101
C1 has value say 98
Suggest a formula in Column “D1” which shows “yes” when the number is between B1 and C1 and “No” when the number is above or below Column B1 and C1.
Thanks
Visu

are u familiar with excel formulas