Solved

excel formula help

Posted on 2011-09-12
22
336 Views
Last Modified: 2012-05-12
I am looking for a formula for the yellow call in column U. if the value on the same row in column T is the lowest of the entire colum T(row 6 and higher), then its value should be 0. Otherwise search for the lowest value in column T and deduct the rows T-value from it.
time-trial-calculator.xls
0
Comment
Question by:stmoritz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
  • +2
22 Comments
 
LVL 50
ID: 36522501
Hello,

by definition, the result will be a negative number. Excel does not display negative time values. In order to see a time value, you will need to convert it to a positive value. This is my suggestion, starting in U6 and copied down

=IF(T6=MIN($T$6:$T$333),0,ABS(INDEX($S$6:$S$333,MATCH(MIN($T$6:$T$333),$T$6:$T$333,0))-T6))

Does that what you expect to see?

If not, please provide a manually calculated result for your sample data.

cheers, teylyn
0
 

Author Comment

by:stmoritz
ID: 36522522
no. now it displays a value of 23:56:43 and oo in the cell s above and below.

V6 should display 00:00:30 (2:47 minus 3:17)
V7 should show 00:00:43 (2:47 minus 3:30)
V8 should display 00:00:00

hope this helps my unclear initial problem description
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36522667
I take it you are trying to work out the time between places so 1st place is 0:00, 2nd is 0:30 and 3rd is 0:43.

Try this:

=IF(V6=1,0,T6-LOOKUP(V6-1,V$6:V$28,T$6:T$28))

Thanks
Rob H
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 33

Expert Comment

by:Rob Henson
ID: 36522676
Oops I only did down to row 28, stretch the $28 down to whatever row is required.

Thanks
Rob H
0
 

Author Comment

by:stmoritz
ID: 36522693
thx Rob. an easy way to avoid the cell reference error when entering the formula?
0
 
LVL 50
ID: 36522717
"V6"??? You asked for a formula for column U.

try in U3 and copied down

=IF(T6=MIN($T$6:$T$333),0,ABS(MIN($T$6:$T$333)-T6))

This produces the results you outlined in your previous comment.

cheers, teylyn
0
 

Author Comment

by:stmoritz
ID: 36522745
thx.

results not correct however

 screenshot
see ID: 36522522

V6 should display 00:00:30 (2:47 minus 3:17)
V7 should show 00:00:43 (2:47 minus 3:30)
V8 should display 00:00:00
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36522749
Sorry, to clarify if I misunderstand the previous comment, are you looking for an easy way to avoid doing what I did in my formula by not including enough rows? Or in other words, is there an amendment to make that will ensure all cells are included?

Taking the row references out altogether so the formula is looking at the whole column would be one approach.

Alternatively you may be able to use a count function to give number of rows and include this within an INDIRECT function but this would be 'clunky' to say the least.

I will have a look.

Thanks
Rob H
0
 

Author Comment

by:stmoritz
ID: 36522755
sorry crrect screensht here scrsht
0
 
LVL 3

Expert Comment

by:K4U
ID: 36522960
formula should be =IF(T7<MIN(T6:T8), 0, MIN(T6:T8))

also attached corrected files. time-trial-calculator.xls
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36522966
Hello stmoritz,

That last screenshot doesn't seem to produce the results you specified earlier - I assume the earilier ones were correct - how about this formula for U6 copied down

=T6-MIN(T$6:T$1000)

regards, barry
0
 
LVL 3

Expert Comment

by:K4U
ID: 36523005

=IF(T6<MIN(T6:T8), 0, ABS((MIN(T6:T8)-T6))) time-trial-calculator--1-.xls time-trial-calculator--1-.xls
0
 

Author Comment

by:stmoritz
ID: 36523126
no solution yet displaying correct results...
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36523164
Hello stmoritz,

Are the results you want definitely as displayed in the last screenshot - they don't match the results you specified earlier? I don't think they match your description either

>if the value on the same row in column T is the lowest of the entire colum T(row 6 and higher), then its value should be 0

regards, barry

0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 125 total points
ID: 36523225
Formula for U6 and copy down:

=IF(T6="","",T6-MIN(T:T))

Gives results:
0:30
0:43
0:00

As described in the post with the Mini.

Cheers
Rob H
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36523266
Hello Rob,

In my post above I suggested almost the same...:)

=T6-MIN(T$6:T$1000)

barry

0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36523321
Barry, indeed you did, hopefully stmoritz will recognise you first.

I only added the option for the value in T being blank from the existing formulas in T and took the whole of column T going on from the issue stmoritz raised in comment 3652293

Thanks
Rob H

0
 

Author Comment

by:stmoritz
ID: 36523336
what am i doing wrong shottime-trial-calculator.xls
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36523379
Which column is the formula in? In your original you had old difference in S , new difference in T and the required formula in U. If you've moved along a column then you are referencing the wrong column. Try using this formula in V6 copied down

=U6-MIN(U$6:U$1000)

regards, barry


0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 125 total points
ID: 36523436
Ok, Rob, I see now.......

I didn't realise that the formula blank would lead to an error - Rob's adjustment then applies too, assuming the above works then to avoid errors further down change to

=IF(U6="","",U6-MIN(U$6:U$1000))

regards, barry
0
 

Author Comment

by:stmoritz
ID: 36523955
this works now. how shall i now fairly allocate points in order everybody's happy. again a big challenge...
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36524220
Thank you stmoritz, I am more than happy to go 50:50 with the great barryhoudini.

Thanks
Rob H
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question