# Search:

Type: Posts; User: AlanY; Keyword(s):

Page 1 of 6 1

1. ## Thread: Calculating Average Speed from hh:mm:ss and known distance

by AlanY
Replies
1
Views
137

### Re: Calculating Average Speed from hh:mm:ss and known distance

have a look if this works for you

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color:...
2. ## Thread: from a list of 12,251 rows and 317 colomns select a list of 78 rows and 317 colomns

by AlanY
Replies
2
Views
149

### Re: from a list of 12,251 rows and 317 colomns select a list of 78 rows and 317 colomns

give it a try

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col...
3. ## Thread: excel match feature

by AlanY
Replies
121
Views
4,622

### Re: excel match feature

I start with the 8 Nos and all draws between 23/6/2018 & 22/6/2019 and click update

<img src="https://www.pixelsbin.com/images/2019/08/12/17e060cd6b49676e0.jpg" alt="17e060cd6b49676e0.jpg"...
4. ## Thread: excel match feature

by AlanY
Replies
121
Views
4,622

### Re: excel match feature

I've modified the file to do one of the first thing you asked for in the earlier post over 2 months ago!
But I didn't get close to your 12 times with 6 out of the 8 Nos in the last 12 months.
...

by AlanY
Replies
121
Views
4,622

got it fixed

6. ## Thread: div error % formula

by AlanY
Replies
3
Views
125

### Re: div error % formula

you're welcome
7. ## Thread: div error % formula

by AlanY
Replies
3
Views
125

### Re: div error % formula

so many ways you can do this, try

=if(isblank(AA63),"",1-(AA63/T63))
8. ## Thread: excel match feature

by AlanY
Replies
121
Views
4,622

### Re: excel filter - formula

after you got the 3 numbers groups with the "Hot3" or "Hot 2+1", you can check the associated Nos and draws by input the 3 Nos in N5:P5 as in post#111 above
9. ## Thread: excel match feature

by AlanY
Replies
121
Views
4,622

### Re: excel filter - formula

did warned you that it takes age!
it took 2300s (almost 40 mins) on mine that's I think you need to pick one number and use the 2+1 option
10. ## Thread: How to organise this data (Year, Vendor, expenses)

by AlanY
Replies
8
Views
261

### Re: How to organise this data (Year, Vendor, expenses)

try this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col...
11. ## Thread: excel match feature

by AlanY
Replies
121
Views
4,622

### Re: excel filter - formula

it shouldn't.
12. ## Thread: Return a value based on several cell values

by AlanY
Replies
3
Views
83

### Re: Return a value based on several cell values

you're welcome
13. ## Thread: Return a value based on several cell values

by AlanY
Replies
3
Views
83

### Re: Return a value based on several cell values

may be

=IF(COUNTIF(AG2:AJ2,"Yes")>0,"Yes",IF(COUNTIF(AG2:AJ2,"No")=4,"No",""))
14. ## Thread: Possible nested functions?

by AlanY
Replies
11
Views
176

### Re: Possible nested functions?

can try

=iferror(IF(INT(E3)=INT(I3), "True","False"),"")
15. ## Thread: Possible nested functions?

by AlanY
Replies
11
Views
176

### Re: Possible nested functions?

you can use the int() function to take out the time value as stored in fraction of a day, try

=if(int(E3)=int(I3),0,1)
16. ## Thread: Possible nested functions?

by AlanY
Replies
11
Views
176

### Re: Possible nested functions?

excel store dates as number, e.g. today 6th Aug 2019 is 43682, so compare dates is just compare numbers.

a simple =if(E3=I3, 0,1) should do it
17. ## Thread: excel match feature

by AlanY
Replies
121
Views
4,622

### Re: excel filter - formula

5) you can check any 3 Nos as before

<img src="https://www.pixelsbin.com/images/2019/08/05/645434369ba60921b.jpg" alt="645434369ba60921b.jpg" border="0">

6) here come the 'Hot 3' that work out...
18. ## Thread: excel match feature

by AlanY
Replies
121
Views
4,622

### Re: excel filter - formula

right, here we go.

I'm going to show you how it works with a small sample from 16/06/2019 to 22/06/2019 3pm
...
19. ## Thread: excel match feature

by AlanY
Replies
121
Views
4,622

### Re: excel filter - formula

I do realise what you're after, I think.

Let me tell what I'm trying to do. with the numbers from 1 to 80 there are 82160 combinations of 3 No set.
I can't think of any workable algorithm apart...
20. ## Thread: Validation for date required with lowest number

by AlanY
Replies
7
Views
192

### Re: Validation for date required with lowest number - Urgent

try

=IF(E3=MINIFS(\$E\$3:\$E\$14,\$A\$3:\$A\$14,A3),TRUE,"")
21. ## Thread: excel match feature

by AlanY
Replies
121
Views
4,622

### Re: excel filter - formula

4) again by eyeballing the results, you can try 66 and click 'check' again, that shows 2 draws with 24 25 and 66

<img src="https://www.pixelsbin.com/images/2019/07/30/4ddbfae19c27b195a.jpg"...
22. ## Thread: excel match feature

by AlanY
Replies
121
Views
4,622

### Re: excel filter - formula

finally got some time to look at it again but unfortunately can't recreate the 12 lines result.
anyway i'd revamp the file to do it differently and may not do all the things you asked for.
...
23. ## Thread: IF Formula/ Countif

by AlanY
Replies
10
Views
320

### Re: IF Formula/ Countif

what's the countif() looks like?

by AlanY
Replies
10
Views
320

ok, may be

25. ## Thread: IF Function with multiple parameters

by AlanY
Replies
5
Views
124

### Re: IF Function with multiple parameters

try

=IF(D3="","",IF(D3<8,100*D3,if(and(D3>8,N2>40%),D3*150,"")))
Results 1 to 25 of 150
Page 1 of 6 1