Formula help - multiple conditions for IF function

Hi
I have a spreadsheet that is updated daily, it is used to track hours/kms of trucks and when the last service was completed.

I need a formula added into column G (HRS truck service due) that will do the following
- if column E "Last service type" equals "A", add D2(HR's/KMs till truck service due)+250
- if column E is "B", add D2+125
- if column E is "C", add D2+125

For example in the attached worksheet G2 will equal 29,398. I want a formula that will add these values automatically. I did try the IF function (and nesting IF Functions) and I don't think it was correct

Thank you!

Originally Posted by 6StringJazzer

Here is the formula you want to use in G2 and copy down, except the values in E are not exactly what you said. Here is the formula based on your description:

Formula:

=D2+IF(E2="A",250,IF(OR(E2="B",E2="C"),125,-D2-999))

This allows for a fourth case where there is an error in E, and it is neither "A" nor "B" nor "C". In such a case the result will be -999 to get your attention.

However, the data you have shows " A" with a space, not "A". It's better to fix that at the source, but the list you are using is in a different file so I can't change it. Here is a formula that assumes you don't fix it.

Formula:

=D2+IF(TRIM(E2)="A",250,IF(OR(TRIM(E2)="B",TRIM(E2)="C"),125,-D2-999))

Thank you Jeff! This worked! I also removed the space after the "A". Ooooops - rookie error! Thank you everyone for all of your help/suggestions

If you are using a data validation list with only A, B, and C, then you don't really need the error check. And if you fix the extra space, you could use a simplified version.

Although the other members' formulas work every bit as well, I had a very specific reason for writing it the way I did. The fact that 250 is 2*125 may be a coincidence, and I avoid taking advantage of coincidences in programming logic. However, I will admit that the fact that B and C correspond the same value may also be a coincidence. So the most loosely coupled formula is

Formula:

=D2+IF(E2="A",250,IF(E2="B",125, IF(E2="C",125)))

This makes it obvious what is happening and which value corresponds to each of A, B, and C. This way if any of the values change later, it is very easy to update the formula. It is not elegant, compact, or clever, but it is very easy to read, understand, and update.

(P.S. It is not necessary to quote the entire post that you are responding to. In this case you didn't really need to quote at all. We have a "rule" about that but it's really more of a guideline.)