Help with IF AND formula - beginner/intermediate user

[MetroA]

Hello,

This is my first post, and I am having problems with a IF AND formula. To give a little background, I use excel in a construction engineering capacity, which is often using excel for quantity takeoffs and cost estimation. Occasionally I do a lookup table or more advanced formula. I feel my skills haven't gotten beyond beginner/intermediate level. That being said, here is my problem.

I have used excel to create a spreadsheet for evaluating fire escape components and created a table (albiet not the best way) to score them across various sheets. That part works fine. I've taken these component scores, created an overall average, and a specific component average (tread and landing), and summarized that in another sheet. Also works fine.

Now here is where i have a problem. I am trying to create a formula that automatically prioritizes these scores.

Average Overall Score </=60 + Tread and Landing Score <10 Priority 1
Average Overall Score >60 but </=65 and Tread and Landing Score <15 Priority 2
Average Overall Score >65 and Tread and Landing Score <20 Priority 3

My formula is this =IF(AND(OR(F3=60,F3<60),G3<6),1,IF(AND(OR(60<F3,60=F3),OR(F3<70,F3=65),G3<13),2,3))
where F3 is the overall score, and G3 is the tread and landing score

When I looked at the results I had one cell with an overall score of 68.5 and a tread and landing score of 14 and it gave me a priority of 2. I would have thought it should have given me a 3 (See Summary sheet row 28) It made me wonder if my formula was correct. If I have to check everything line by line it sort of defeats my purpose.

I am looking at the way I scored things to see if it makes sense or need to make adjustments. Hopefully I can just change a value here or there to correct the problem. Ive attached the spread sheet (created in 2007 but Im working in 2003) if anyone can advise me if my formula is correct. I noticed the problem in the summary sheet row H.

Thanks for any help you can provide.

[arthurbr]

Hi and welcome to the board

What happens, if, say the Overall Score is <60 and the Tread Score is >10?

Do these results apply to real life, and do they involve security issues?

[MetroA]

Hi, and thanks for responding. To answer your first question, I am re-evaluating the scoring criteria. The Average Scores came from another project and might as well been hand written. The numbers are somewhat arbitrary, and are just used to create some type of ranking. In the previous sheets I assigned values of 3.3333 for G 2.3332 for F 1.3333 for P 0.3333 for VP (Good, Fair, Poor, Very Poor) on previous sheets, totaled the score then took averages. When I look at the summary I have a lowest overall score average of 32.25 and a highest of 68.5, so I'm thinking I need to rework that. Same with the the tread and landing. a low of 5 and a high of 14.

There are no security issues and the results only apply to determine which of these get a maintenance priority. If I had confidence that the formula is correctly written, I think I could look at the scoring.

[arthurbr]

Following your example ( sheet and description), try

Please Login or Register  to view this content.

[MetroA]

Just some more thoughts the tread and land scoring in the summary should probably be something like 5, 9.5, and 14 (priority 1, 2, 3 respectively)
overall scoring should probably be something like 32.25, 50.5 and 68.5 (priority 1, 2, 3 respectively), with the worse of the 2 conditions taking priority. Does that make sense?

[arthurbr]

You can replace these values in the formula provided.
What do you mean by " worst of the two conditions "?

[MetroA]

I tried your formula, and got several cells to return a value of false.

[MetroA]

Hopefully I can explain this in real terms. These values may or may not exist in my data. Lets say the overall score is 61 and the tread and landing score is 6. To me that means the even though the real world physical condition is generally pretty good, the treads and landings are in poor shape. That would take priority. As a mater of practicality, you could't wall on the landing or stairs safely even if the remaining portions of the structure (framing, guard rails, bolts, connections, welds, etc) are in good condition. Does that make sense?

[arthurbr]

I tried your formula, and got several cells to return a value of false.

That is because some results are of the same type I already mentioned

What happens, if, say the Overall Score is <60 and the Tread Score is >10?

[arthurbr]

Hopefully I can explain this in real terms. These values may or may not exist in my data. Lets say the overall score is 61 and the tread and landing score is 6. To me that means the even though the real world physical condition is generally pretty good, the treads and landings are in poor shape. That would take priority. As a mater of practicality, you could't wall on the landing or stairs safely even if the remaining portions of the structure (framing, guard rails, bolts, connections, welds, etc) are in good condition. Does that make sense?

It does make sense, but how would it be interpreted mathematically ?

[MetroA]

Thank you for your help. I believe using your formula and some revision I found a balance between mathematical representation and common sense. I did have to do a few overrides, but I can live with it. Thanks again