need a shorter formula

N

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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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
 
Upvote 0
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)
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
niyrho said:
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.
Click to expand...

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))
 
Upvote 0
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.
 
Upvote 0
niyrho said:
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.
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Help to sum all cells above until blank cell
Replies
3
Views
168
RemyMoo
R
L
  • Solved
Formula for IF to calculate and add and subtract production remaining
Replies
3
Views
366
Legacy 143009
L
J
  • Question
Concatenating decimal numbers and character to display fractions in another cell.
Replies
3
Views
239
Joek88
J
S
  • Question
Formula for Equity Vesting
Replies
6
Views
301
Dave Patton
D
N
  • Question
Count - specific consecutive order
Replies
6
Views
496
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,642
Messages
6,120,701
Members
448,980
Latest member
CarlosWin

Share this page

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