need to shorten an if formula

My formula is:

=IF(E4="Break",D4,IF(H4="Break",G4,IF(K4="Break",J4,IF(N4="Break",M4,IF(Q4="Break",P4,0)))))

My problem is that excel only lets me use 7 IF functions and I need a total of 12. So I need a different way to perform that same function.

One other compound formula you could use is:

=INDEX(A4:Q4,1,MATCH("Break",A5:Q5)-1)

However if you describe what you're trying to achieve there may be other ways of getting there.

Regards

If the intermediate cells, F4, G4 etc. won't contain "Break"

=INDEX(D4:AK4,MATCH("Break",E4:AL4,0))

otherwise

=INDEX(D4:AK4,MIN(IF(E4:AL4="Break",IF(MOD(COLUMN(E4:AL4)-COLUMN(E4),3)=0,COLUMN(E4:AL4)-COLUMN(E4)+1))))

confirmed with CTRL+SHIFT+ENTER

That code just gave me an #N/A.

This is for a time sheet. The cell the code is in is Sheet2!C14. All the info is entered on sheet 3. This code needs to look for the first "Break" entry and take the value of the cell next to it, which is the "time out" for the last job.

If we only do 7 or fewer jobs in one day the =if functions work fine. But when we do an inspection run we'll have up to 12 jobs in day.

Anyway, what I'm basicly doing is calculating times according to policy. Sheet 2 is the time sheet that actualy gets sent in to accounting. Sheet 3 is where all the info is entered and where most of it is calculated.

This code : =INDEX(D4:AK4,MATCH("Break",E4:AL4,0)) ,kinda worked. I had to replace ,0)) with ,-1)). I also tried 1, 2, 0, -2, -3. The negative ones worked but gave me the value to the right instead of the left. The other numbers just gave me #N/A.

The formula works if an ISERROR is added, see below:

Alt.1: =IF(ISERROR(formula),0,formula)
Alt.2: =CHOOSE(LEN((E4="break")*10^5+(H4="Break")*10^4+(K4="Break")*10^3+(N4="Break")*10^2+(Q4="Break")*10^1),0,P4,M4,J4,G4,D4)

Also see encl. file

Hope it helped
Ola

Originally Posted by niyrho

This code : =INDEX(D4:AK4,MATCH("Break",E4:AL4,0)) ,kinda worked. I had to replace ,0)) with ,-1))

I don't see how replacing the 0 with -1 will work. That might give you odd results. Using the formula I suggested if the first appearance of "Break" is in Q4 the formula will return the value in P4, isn't that what you wanted? Doesn't that work for you?

If you're using MATCH to get an exact match then you need 0 as the third argument

daddylonglegs:

I don't know why it didn't work. It just didn't. I put it in just as you said and got the error. So I played with it a lil and it still wouldn't work.

You other guys, I don't understand what you're telling me at all.

Well if

=INDEX(D4:AK4,MATCH("Break",E4:AL4,0))

gave an error (#N/A?) then that would suggest "Break" doesn't occur in any of those cells. It works with -1 because that will give you a "closest" match.....but as your data is [presumably] unsorted it's not a reliable way to go.

Does "Break" occur in a cell with other values? try using

=INDEX(D4:AK4,MATCH("*Break*",E4:AL4,0))

Sorry we've been unclear.

I think the problem is When there is no "Break" at all.
In that case the MATCH formula will generate a #N/A error, which has to be avoided by including an ISERROR() formula.
Your original formula had a 'If no "Break" then 0'. But I think some suggestions missed that.

The easiest is to look at the formulas in the enclosed file (see above post).
I tried all options (for the your example) and it works - unless the original file (with 12 if) has something which "complicates things"....

Hope this helped
Ola

daddylonglegs:

I figured out your formula. I made a mistake typing it in. Still isn't working for me though. Its returning "Break" instead of the time value next to it.

olasa:

I have to do this kinda blind. I'm working on this on another computer. Can't open the file on this one and the other I can't put online.

It sounds like it's still mistyped. Try copying and pasting from the post.

I can't copy and paste it. It's on a different computer.