need a shorter formula

niyrho

Board Regular
Joined
Jun 19, 2008
Messages
108
I need another way to do this:

=IF(E5=0,D4,IF(H5=0,G4,IF(K5=0,J4,IF(N5=0,M4,""))))

This formula works for me, but I can't make it long enough. It only allows me to use 7 IF funtions. I have more than that to put it, and it has to look them up in order.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
are there likely to be 0's in F5, H5 etc (ie the ranges you're ignoring) ?

If not you could use something like:

=INDEX($4:$4,0,3+MATCH(0,$E$5:$AZ$5,0))

This would error if no 0 was found though...

If this isn't going to work for you post back with more info and possible some sample data
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Adjusting lasw's idea to allow for the possibilities of 0's in F5, H5, etc.:

=INDEX(4:4,MIN(IF(E5:AZ5=0,IF(MOD(COLUMN(E5:AZ5),3)=MOD(COLUMN(E5),3),COLUMN(E5:AZ5))))-1)

which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar)
 

niyrho

Board Regular
Joined
Jun 19, 2008
Messages
108

ADVERTISEMENT

I don't understand how that formula works. I couldn't get it to work right.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Please let us know *what* about it is not working. It will be easier for us to help or adjust the solution if we know what's broken...
 

niyrho

Board Regular
Joined
Jun 19, 2008
Messages
108

ADVERTISEMENT

I'm not sure. I just couldn't get it to do anything close to what I need.
I'll try to explain exactly how this is laid out.

Sheet1 is the time sheet. Cell B14 is time in, and just referances Sheet3 B4. C14 is time out. That one changes from day to day. It has to look for the first break you took and = the time it started.

Sheet3 is where all the info is entered. Its broked up into groups of 5 cells each; job time in(C4), job time out(D4), trv/break(E4), job name(C5), and trv time(E5). And they're repeated accross the page, 12 times.

On Sheet1, cell C14 needs to find the first "break" or "lunch" or anything other than "travel". Thats when the cut-off time is. The time doesn't count if you're on break, so if E14 = "break" then your time out would be right next to it in "job time out, D4.

So on Sheet1, cell C14 has to look at row 4 on sheet3, find "break" and take the value of the cell next to it.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
I need another way to do this:

=IF(E5=0,D4,IF(H5=0,G4,IF(K5=0,J4,IF(N5=0,M4,""))))

This formula works for me, but I can't make it long enough. It only allows me to use 7 IF funtions. I have more than that to put it, and it has to look them up in order.

Hi niyrho:

Let me see if I have understood you correctly ...
Code:
=INDEX(D4:IU4,MATCH(0,INDEX(IF(MOD(COLUMN(E5:IV5)+1,3)=0,E5:IV5),0),0))
 

niyrho

Board Regular
Joined
Jun 19, 2008
Messages
108
That one is giving me nothing but an #N/A. I'm goin kinda blind here. Can you tell me what the parts of the code are? I tried using excel help, but, well, they might as well have left that whole thing out.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
That one is giving me nothing but an #N/A. I'm goin kinda blind here. Can you tell me what the parts of the code are? I tried using excel help, but, well, they might as well have left that whole thing out.

Hi niyrho:

Please look at the following ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFGHIJKLMN
3result**data
4_G2**_G1**_G2**_G3****
5***375kj045930***
Sheet3


</body></html>

and then tell me

1) is this what you are looking for?
2) if not, then for the data posted, what is the result you are seeking along with an explanation as to why you think that is the correct result
3) and if this is entirely in the left field, please reexplain your question, so it can be clearly understood
 

Watch MrExcel Video

Forum statistics

Threads
1,122,331
Messages
5,595,541
Members
413,996
Latest member
mabelO

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
Top