• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

excel formula help

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
stmoritz
Asked:
stmoritz
  • 7
  • 6
  • 5
  • +2
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
stmoritzAuthor Commented:
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
 
Rob HensonFinance AnalystCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rob HensonFinance AnalystCommented:
Oops I only did down to row 28, stretch the $28 down to whatever row is required.

Thanks
Rob H
0
 
stmoritzAuthor Commented:
thx Rob. an easy way to avoid the cell reference error when entering the formula?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
"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
 
stmoritzAuthor Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
stmoritzAuthor Commented:
sorry crrect screensht here scrsht
0
 
K4UCommented:
formula should be =IF(T7<MIN(T6:T8), 0, MIN(T6:T8))

also attached corrected files. time-trial-calculator.xls
0
 
barry houdiniCommented:
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
 
K4UCommented:

=IF(T6<MIN(T6:T8), 0, ABS((MIN(T6:T8)-T6))) time-trial-calculator--1-.xls time-trial-calculator--1-.xls
0
 
stmoritzAuthor Commented:
no solution yet displaying correct results...
0
 
barry houdiniCommented:
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
 
Rob HensonFinance AnalystCommented:
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
 
barry houdiniCommented:
Hello Rob,

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

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

barry

0
 
Rob HensonFinance AnalystCommented:
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
 
stmoritzAuthor Commented:
what am i doing wrong shottime-trial-calculator.xls
0
 
barry houdiniCommented:
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
 
barry houdiniCommented:
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
 
stmoritzAuthor Commented:
this works now. how shall i now fairly allocate points in order everybody's happy. again a big challenge...
0
 
Rob HensonFinance AnalystCommented:
Thank you stmoritz, I am more than happy to go 50:50 with the great barryhoudini.

Thanks
Rob H
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now