IF function (I'M Lost)

Millj

New Member
Joined
Sep 24, 2002
Messages
6
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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!
 
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,070
Members
453,593
Latest member
Mubashar Ali

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