# IF function (I'M Lost)

#### Millj

##### New Member
Hi Everyone,

Heres the problem. I'm trying to schedule maintenance based on the number of times the
equipment starts. But the type of Maintenance varies based on the the number of Equipment starts (which vary year to year).

assume: 400 starts = "A"
800 starts = "A"
1200 starts = "B"
1600 starts = "A"
2000 starts = "A"
2400 starts = "C"

years: 1 2 3 4 5

Starts 100 404 443 1250 2460
cells c19 d19 e19 f19 g19

the current formula I'm using works but only
for one event. (See Below) I'm not real good at nesting and I'm not sure thats the answer.

=IF(AND(399<C19,C19<450), "A", "----")

result in c19 would be "----"

If applied to the other cells the
result in d19 would be "A"
result in e19 would be "A" (also a problem)
result in f19 would be "----"
result in g19 would be "----"

Basically, Looking for a way to check for all
the above assumptions and return the correct
event (A,B or C) with out duplication of a event like e19 shows.

Anybody got any ideas?

Jim

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi Jim,
It sounds like a job for the VLOOKUP function!

Your C19 contains 100, so in C20 try the formula =VLOOKUP(C19,rngTable,2,TRUE).
What this says is, compare C19 against the first column of a range of values within rngTable (which are in ascending sequence) and find a match when the rngTable element equals or first exceeds the value in C19 (the TRUE part means inexact referrals permitted), and then shift over to the equivalent element in column 2 of rngTable and insert that value into the current cell.

Before any of this will work you'll need to set up rngTable (it can go anywhere convenient on your wksh/wkbk) as a table with the first column equal to the number of starts/maintenance breakpoints, eg 400, 800, etc. Against each of those, and in the second column type the maintenance type letter, ie A, A, B, etc - almost exactly as you laid out the data in your question/post. Now give that six by two cell 'table' a range name of rngTable (Insert|Name|Define).

Finally you can type the formula, as first mentioned, into C20 and copy it through G20. Et voila!

Regards
=dn
BTW: I have assumed that the numbers of starts are already cumulative over the years!

Replies
41
Views
2K
Replies
0
Views
271
Replies
1
Views
450
Replies
2
Views
556
Replies
7
Views
342

1,203,453
Messages
6,055,530
Members
444,794
Latest member
HSAL

### 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?

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