# The most pointless combination of functions



## Lewiy (Jul 31, 2008)

Is there anything more pointless than this:
=ISLOGICAL(ISLOGICAL(A1))


----------



## Richard Schollar (Jul 31, 2008)

This is pretty pointless:

=ISNUMBER(1)

or

=SUM(A1)

But these suffer from brevity, whereas yours is truly an example of utter irrelevancy


----------



## Legacy 96851 (Jul 31, 2008)

My boss once gave me a sheet where he had used 

=SUM(D4:D17)/COUNT(D4:D17)

quite a few times.


----------



## Jonmo1 (Jul 31, 2008)

> My boss once gave me a sheet where he had used
> 
> =SUM(D4:D17)/COUNT(D4:D17)
> 
> quite a few times.


 
I wouldn't call that one pointless....
It does in fact serve a purpose, it gives you the average...

It's overcomplicated and could be done with a simpler formula, but it's not pointless...


----------



## Legacy 96851 (Jul 31, 2008)

jonmo1 said:


> I wouldn't call that one pointless....
> It does in fact serve a purpose, it gives you the average...
> 
> It's overcomplicated and could be done with a simpler formula, but it's not pointless...


 
Its very existence is unnecessary, and for that, I would call it pointless. The topic, after all, is pointless combinations.


----------



## Jonmo1 (Jul 31, 2008)

I would say we need a clearer definition of the word "Pointless"

I (and I beleive Lewiy as well) was considering the definition : Does Nothing, Serves no purpose

You seem to include "can be done different/better/easier" in the definition...

By that definition I would say Vlookup is pointless.


----------



## MrKowz (Jul 31, 2008)

=If(1=1,1,1)

Pointless


----------



## DonkeyOte (Jul 31, 2008)

MrKowz, shouldn't that be:

=IF(1=1,1*1,1/1)


----------



## Jonmo1 (Jul 31, 2008)

=and(a1=1,a1<>5)


----------



## Legacy 96851 (Jul 31, 2008)

jonmo1 said:


> I would say we need a clearer definition of the word "Pointless"
> 
> I (and I beleive Lewiy as well) was considering the definition : Does Nothing, Serves no purpose
> 
> ...


 
I was trying to be creative and think up something someone might actually use  - something that goes out of its way without object.

If we're just doing functions that do nothing, how about:

=OR(NOT(NOT(TRUE)), NOT(NOT(NOT(FALSE))))


----------



## Jonmo1 (Jul 31, 2008)

> I was trying to be creative and think up something someone might actually use  - something that goes out of its way without object.


 
Fair enough, wasn't trying to start an argument. We're cool...


I've actually seen this many times, both in work and on this forumn

=IF(condition,"TRUE","FALSE")


----------



## Norie (Jul 31, 2008)

jonmo

That's not pointless, well it is I suppose.

But perhaps what is required *is* the text value, rather than TRUE/FALSE which are really only numeric as far as I'm aware.


----------



## MrKowz (Jul 31, 2008)

lasw10 said:


> MrKowz, shouldn't that be:
> 
> =IF(1=1,1*1,1/1)


 
=IF(abs(1)=sqrt(1),1^1,(1*1+1-1)/(sqrt(1)*abs(1)*1^1))

lets see how many times we can get 1 to appear in an if statement.


----------



## Jonmo1 (Jul 31, 2008)

> But perhaps what is required *is* the text value, rather than TRUE/FALSE


 
But the logical TRUE/FALSE is still useable as a text string..
You can copy/paste it into notepad
=""&A1 results in a text string of "TRUE" where A1 is a logical resulting in TRUE.

The only place it doesn't work as a text string is in a lookup formula.
=LOOKUP("TRUE",A:B) doesn't work.
But all you have to do is drop the quotes and it does.


----------



## Legacy 96851 (Jul 31, 2008)

jonmo1 said:


> Fair enough, wasn't trying to start an argument. We're cool...


 
Ah please, we're always cool. This is, after all, the internet.

Who knew pointless combinations of functions could spark such heated debate?


----------



## MrKowz (Jul 31, 2008)

=if(isblank(a1),"","")


----------



## erik.van.geit (Jul 31, 2008)

make it
=if(isblank(a1),a1,"")

because it 'looks' more usefull


----------



## Lewiy (Aug 1, 2008)

The thing that actually inspired me about
=ISLOGICAL(ISLOGICAL(A1))
Is that it only has one reference to a cell, there are no “user defined” results and it makes absolutely no difference what the value of A1 is, it can be a number, a text string, an error value….anything.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
So I guess, my definition of a pointless formula, would be one that serves no purpose regardless of the input value and that produces its own result rather than having a user define the result.


----------



## xlHammer (Aug 1, 2008)

FIND(carkeys,now)

never seems to work.

of course TRUNC(A1) is certainly decimal pointless.

(sorry)

Rob


----------



## riaz (Aug 1, 2008)

xlHammer said:


> FIND(carkeys,now)
> 
> never seems to work.
> 
> ...



Hi Rob

Did you try FINDing carkeys in the TRUNC?


----------



## Jonmo1 (Aug 1, 2008)

> Hi Rob
> 
> Did you try FINDing carkeys in the TRUNC?


 
That would cause a circular reference, - need the keys to open the trunc....


----------



## xlHammer (Aug 1, 2008)

I'd tried the formula FIND(carkeys,TRUNC(A911)) but all I got was the error message
#GOHOME?

I realise what I've done wrong though, I haven't got a 911.


----------



## riaz (Aug 1, 2008)

A home is not a home without a porch.


----------



## erik.van.geit (Aug 1, 2008)

short and pointless
=N(""&A1)

EDIT: variation
=IF(N(""&A1),N(A1),0)


----------



## Oorang (Aug 1, 2008)

Technically there is a difference between =IF(A1=B1,"TRUE","FALSE") and =A1=B1. The former returns a variant/string whereas the later returns a variant/boolean. It would be unusual for such a technicality to make a serious difference (or any really) but in principle it could.  One example of how they could be treated differently is in excel 2003 the auto-formatting will automatically left align the string version but not the boolean.


----------



## Lewiy (Aug 1, 2008)

Oorang said:


> Technically there is a difference between =IF(A1=B1,"TRUE","FALSE") and =A1=B1. The former returns a variant/string whereas the later returns a variant/boolean. It would be unusual for such a technicality to make a serious difference (or any really) but in principle it could.  One example of how they could be treated differently is in excel 2003 the auto-formatting will automatically left align the string version but not the boolean.



Interesting that we appear to have gone round in a big circle here because to prove Oorang's point:

=ISLOGICAL(IF(A1=B1,"TRUE","FALSE"))

returns FALSE!!!


----------



## Peter_SSs (Aug 1, 2008)

The most pointless combination of function?
There isn't one, and I think this thread is well on the way to demonstrating that. Sort of like below  

```
Step1:
    Whenever you think you have the most pointless combination,
    somebody can think of another one that is either
    a) more pointless, or
    b) equally pointless.

If a Then
    You now have the second most poinless combination
ElseIf b Then
    You now only have an equal most pointless combination, not *the *most pointless
End If

GoTo Step1
```


----------



## mikerickson (Aug 3, 2008)

```
If (Range("A1") < 2) = True then
    Range("A2")= "small"
End If
If (Range("A1") < 2) = False then
    Range("A2")= "Big"
End If
```


----------



## Gerald Higgins (Aug 11, 2008)

My humble attempt, combining some of Lewiy's and Erik's ideas into something that looks complicated but does nothing.


```
=IF(A1=OFFSET(A1,N(""&A1),N(""&A1),ISLOGICAL(ISLOGICAL(A1)),
ISLOGICAL(ISLOGICAL(A1))),SUBSTITUTE(A1,A1,"",1),LEFT(A1,N(""&A1)))
```


----------



## Jonmo1 (Aug 11, 2008)

How about

=A1*0


----------



## arkusM (Aug 14, 2008)

maybe "pointless" is simply a level of "having a point" that we do not yet understand


----------



## Oorang (Aug 14, 2008)

Might be more fun to do the _seemingly_ pointless functions. Like did you know: 
	
	
	
	
	
	



```
Range("A1").Value = Range("A1").Value
```
 actually does something? (Free cookie to the first person who knows the value of it )


----------



## MrKowz (Aug 14, 2008)

Oorang said:


> Might be more fun to do the _seemingly_ pointless functions. Like did you know:
> 
> 
> 
> ...



=A1?

(circular reference mind boggling!)


```
Sub Pointless()
    Dim i as integer
    Dim LR as integer: LR = Range("A" & rows.count).End(xlUp).Row
    For i = 1 to LR
        If Cells(i + LR,"A") = Cells(LR + 1,"A") Then
        Msgbox "Press OK to Continue"
    Next i
End Sub
```


----------



## Cindy Ellis (Aug 15, 2008)

Oorang said:


> Might be more fun to do the _seemingly_ pointless functions. Like did you know:
> 
> 
> 
> ...



Assuming A1 had a formula in it, wouldn't this replace the formula with the value of the function at that moment?
--Cindy


----------



## Lewiy (Aug 15, 2008)

It performs the act of PasteValues


----------



## Lewiy (Aug 15, 2008)

Perhaps a challenge to find the most interesting results of a function or formula, where ALL of the arguments are “A1”.  To kick things off, can anyone tell me which of the following is the odd one out:
=REPLACE(A1,A1,A1,A1)
=SUBSTITUTE(A1,A1,A1,A1)
=LEFT(A1,A1)
=TEXT(A1,A1)


----------



## kgkev (Aug 15, 2008)

I've always like multile random numbers in function

=IF(RANDBETWEEN(1,3)=1,1,IF(RANDBETWEEN(1,3)=2,2,IF(RANDBETWEEN(1,3)=3,3,0)))

Maybe more pointless would be

=IF(RANDBETWEEN(1,3)=1,1,IF(RANDBETWEEN(1,3)=2,2,IF(RANDBETWEEN(1,3)=3,3,randbetween(1,3))))


----------



## kgkev (Aug 15, 2008)

got another one

=left(a1,len(a1))


----------



## Gerald Higgins (Aug 15, 2008)

On the random theme

```
=if(rand()=rand(),rand(),rand())
```
Strictly speaking, you could argue this does actually does SOMETHING, but nothing very useful.

Lewiy - post #36
I tested these - as far as I can tell there are several odd ones out.
The TEXT formula is the only one that doesn't return an error if A1 contains text.

If A1 contains 0, TEXT returns 0 and LEFT returns blank, the others return errors.
If A1 contains a 2 digit number, or a decimal such as 1.1, thing start to get interesting. Also the results for A1=10 are different from A1=11.


----------



## barry houdini (Aug 15, 2008)

kgkev said:


> Maybe more pointless would be
> 
> =IF(RANDBETWEEN(1,3)=1,1,IF(RANDBETWEEN(1,3)=2,2,IF(RANDBETWEEN(1,3)=3,3,randbetween(1,3))))


 
I don't think this qualifies as pointless. It produces a specific distribution, i.e. more 1s than 2s and more 2s than 3s [in fact, if you have that formula in 81 cells then, on average, you'll get 35 *1*s, 26 *2*s and 20 *3*s] so if you require that distribution the point of the formula would be to produce it!

Time for my tablet, I think.................


----------



## Lewiy (Aug 15, 2008)

> I tested these - as far as I can tell there are several odd ones out.
> The TEXT formula is the only one that doesn't return an error if A1 contains text.
> 
> If A1 contains 0, TEXT returns 0 and LEFT returns blank, the others return errors.
> If A1 contains a 2 digit number, or a decimal such as 1.1, thing start to get interesting. Also the results for A1=10 are different from A1=11.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>



<o> </o>
You are quite right, there are various different results you can get from each depending on what value is entered in A1, however, the solution is a little deeper than that.  The correct answer is SUBSTITUTE and here’s why:
<o> </o>
No matter what value/data type is in A1, be it an integer, a decimal, a text string, a boolean value or an error value, SUBSTITUTE is the only one that never has a result which is different from all the other three, there is always at least one of the other formulas which returns the same value as SUBSTITUTE.  All of the others have uniquely different results from certain A1 values.


----------



## Gerald Higgins (Aug 15, 2008)

barry houdini said:


> It produces a specific distribution, i.e. more 1s than 2s and more 2s than 3s


 
Barry - you're absolutely right, although it took me a while to work out why - I was too hung up on RANDBETWEEN generating truly random numbers.

Of course it clicked when I remembered that the various instances of RANDBETWEEN in the formula are all returning different values - that's an important point to bear in mind when using RAND functions - using them sequentially might lead to non-random results.


----------



## kgkev (Aug 15, 2008)

I can't see why its not random?

Each step has the same probability of generating each number

set 1 has a 1 in 3 chance of producing a 1

set 2 is the completely independant of set 1 and has a 1 in 3 chance of producing a 2

then stage 3 is completely independant of the previous 2 stages and has a 1in 3 chance of producing a 3

After all these stages there is a 1-3 chance of any of the numbers being generated.

Can you explain why this is not the case?

perhaps this would be more random?


=IF(RANDBETWEEN(1,3)=1,RANDBETWEEN(1,3),IF(RANDBETWEEN(1,3)=2,RANDBETWEEN(1,3),IF(RANDBETWEEN(1,3)=3,RANDBETWEEN(1,3),randbetween(1,3))))

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>


----------



## cornflakegirl (Aug 15, 2008)

barry houdini said:


> Time for my tablet, I think



Barry - can I be a geek, and ask how you worked out the distribution of 1s, 2s and 3s, please. Because I just spent 10 minutes creating a nice little tree, and I'm sure there must be a better way. But A level stats was a long time ago... Only if you haven't taken your tablet yet, obviously


----------



## cornflakegirl (Aug 15, 2008)

kgkev said:


> set 2 is the completely independant of set 1 and has a 1 in 3 chance of producing a 2]



set 2 is completely independent - but the 2 will only trigger if set 1 _didn't_ produce a 1.

Take yourself 81 lines in a spreadsheet, and write out all the outcomes of the 4 RANDBETWEENs, and the answer each produces - then it will be obvious!


----------



## Gerald Higgins (Aug 15, 2008)

Set 2 is not completely independent of set 1. It has a 1 in 3 chance of producing a 2, if it is reached in the first place. But it only has a 2 in 3 chance of being reached at all - it won't be reached at all if set 1 evaluates to 1.

The probabilities of each part of the formula are something like this
Return 1 at stage 1 - 33%
Return 2 at stage 2 - 22% (67% chance of reaching stage 2, multiplied by 33% chance of returning a 2 if stage 2 is reached)
Return 3 at stage 3 - 15% (44% chance of reaching stage 3, multiplied by 33% chance of returning a 3 if stage 3 is reached)
Return 1 at stage 4 - 10% (30% chance of reaching stage 4, divided by 3)
Return 2 at stage 4 - 10%
Return 3 at stage 4 - 10%.

Adding all these up gives you probabilities of
43% for 1
32% for 2
25% for 3

Note, IANAS, so I may not have got this quite right


----------



## Gerald Higgins (Aug 15, 2008)

Testing this empirically, the values do seem to trend towards what I suggested


----------



## cornflakegirl (Aug 15, 2008)

Gerald Higgins said:


> Set 2 is not completely independent of set 1. It has a 1 in 3 chance of producing a 2, if it is reached in the first place. But it only has a 2 in 3 chance of being reached at all - it won't be reached at all if set 1 evaluates to 1.



Gerald - I think this is just a terminology thing. I'm assuming that you evaluate all four functions, and then work out what the answer is. In which case set 2 is independent of set 1.

But IANASE, so I could just be confusing myself!


----------



## cornflakegirl (Aug 15, 2008)

Oh, and your values agree with what Barry posted: 35/81 = 43%, 26/81 = 32%, 20/81 = 25%.


----------



## Gerald Higgins (Aug 15, 2008)

OK perhaps it is a terminology thing.
Step 2 will evaluate to 1, 2 or 3 completely randomly, with each having a probability of 33%. In that sense, it is independent of Step 1
BUT, the result of step 2 will only affect the overall result of the formula, IF Step 1 has NOT evaluated to 1. In that sense, Step 2 is dependent on the results of Step 1.


----------



## cornflakegirl (Aug 15, 2008)

Glad we got that one sorted!


----------



## Lewiy (Aug 15, 2008)

> =IF(RANDBETWEEN(1,3)=1,1,IF(RANDBETWEEN(1,3)=2,2,IF(RANDBETWEEN(1,3)=3,3,RANDBETWEEN(1,3))))



<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
For those of you who are interested in the statistics, I created a list of 5000 of the above formula and counted the frequency of 1s, 2s and 3s.  I repeated this 20 times and got the following results:
<o> </o>
Average percentage of 1s: 43.143%
Average percentage of 2s: 32.184%
Average percentage of 3s: 24.673%
<o> </o>
Standard Deviation of 1s: 0.006262
Standard Deviation of 2s: 0.006368
Standard Deviation of 3s: 0.005044
<o> </o>
Total variation of percentages (highest percentage minus lowest percentage)
1s: 2.12%
2s: 2.52%
3s: 2.12%
<o> </o>
Conclusion……This seems to be a fairly consistently distributed random function!!!


----------



## barry houdini (Aug 15, 2008)

cornflakegirl said:


> Barry - can I be a geek, and ask how you worked out the distribution of 1s, 2s and 3s, please. Because I just spent 10 minutes creating a nice little tree, and I'm sure there must be a better way. But A level stats was a long time ago... Only if you haven't taken your tablet yet, obviously


 
I've only taken the little blue one so I'm OK for now.......

I think Gerald's done it for you but obviously I used 81 to keep to whole numbers.

Run the formula 81 times and 27 times the 1st Randbetween generates 1, formula over

For the 54 times that 1st RB generates 2 or 3, 2nd RB is brought in to play and 54/3 = 18 2s, formula over

For the remaining 36 times that RB #3 runs we get 36/3 = 12 3s, formula over

Then 24 times RB 4 is called there are 8 each of 1 , 2 and 3

so the totals are

1 - 27+8 =35 = 43.21%
2 - 18+8 =26 = 32.10%
3 - 12+8 =20 = 24.69%

Edit: which almost exactly matches the distribution that Lewiy experienced


----------



## kgkev (Aug 15, 2008)

Its now clear that I have generated a pointless explaination of a pointless distibution generated by a pointless statement.

Sorry.

Thanks for explaining though - Its all a bit cleared now and i will try to avoid calling rand() functions multiple times within the same function.


----------



## cornflakegirl (Aug 15, 2008)

Yeah, I realised when Gerald posted that that method wasn't actually as involved as I had thought when I gave up on it


----------



## cornflakegirl (Aug 15, 2008)

kgkev - I, at least, enjoyed the discussion. And I don't think you need to apologise for inanities in the Lounge - I'm in trouble otherwise!


----------



## barry houdini (Aug 15, 2008)

kgkev said:


> Its now clear that I have generated a pointless explaination of a pointless distibution generated by a pointless statement.


 
Congratulations! 



kgkev said:


> .......and i will try to avoid calling rand() functions multiple times within the same function.


 
I enjoyed it, at least, took my mind off work for ½ an hour.........


----------



## Oorang (Aug 15, 2008)

/me hands Cindy Ellis the cookie


----------



## Norie (Aug 15, 2008)

Is this pointless?

=0

Is it a formula?


----------



## framednlv (Aug 15, 2008)

Norie said:


> Is this pointless?
> 
> =0
> 
> Is it a formula?


SUM(B1:H1)^0
Just trying to one up you!

Chris


----------



## Cbrine (Aug 15, 2008)

How about this one, following in Norie's footsteps....simple with absolutly no effect
=""


----------



## Oorang (Aug 15, 2008)

Well uhm I feel like I might be nitpicking here, but I'll point out that it changes the value from "Empty" to a zero length string... Which is occasionally desirable. I have actually intentionally used ="" with copy/pastespecialvalues to ensure that when I saved the file as a delimited file, those cells would not be imported as null. (Empty cells on the right of a used range do not get delimiters the data just ends. And many programs that import them then go "Oh it's a null field")


----------



## jproffer (Aug 17, 2008)

> I'd tried the formula FIND(carkeys,TRUNC(A911)) but all I got was the error message
> #GOHOME?
> 
> I realise what I've done wrong though, I haven't got a 911.



That complex of a command is only possible in VBA. Try:


```
Sub Find_Keys
With ActiveHouse
   Set x =.Find(CarKeys, lookin:=xlCouchCushions)
If Not x IsNothing Then
 Range(x.Address).Copy
 Range(MyHand.Address).PasteSpecial(xlPasteAll)
y=MsgBox("Keys have been found")
End Sub
```


----------



## Andrew Fergus (Aug 18, 2008)

jonmo1 said:


> How about
> 
> =A1*0


I have seen legitmate variations of this where the first half of the formula is somewhat more complicated than "A1".  For instance, if you have a calculated value that you wish to eliminate the effect of, without losing the formula, then this is one way of doing it - particularly where there are multiple references to that value.  So whilst the impact may be pointless, it can still be a useful technique.

Andrew


----------



## xlHammer (Aug 18, 2008)

jproffer said:


> That complex of a command is only possible in VBA. Try:
> 
> 
> ```
> ...


 

This is very useful, thanks. However I have Wife 2.0 installed on my system so I can't access the ActiveHouse without first unloading the Dishwasher array. That said I have on more than one occasion found the CarKeys in explicably stored in the HandBag cache (?!!) so it might be worth inserting a Call Missus() module first.


----------



## Lewiy (Aug 18, 2008)

Perhaps this will solve your problem:


```

```


```

```


```
Sub Find_CarKeys()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Dim Missus As Object, CarKeys As Object, Home As Object, <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:City w:st="on"><st1:place w:st="on">Mobile</st1:place></st1:City> As Object<o:p></o:p>
Dim Door As Object, DishWasher As Object, Dishes() As Object<o:p></o:p>
Dim Nagging As Long, c As Long, i As Long, Patience As Long<o:p></o:p>
Dim AcceptableTimeToAsk As Boolean<o:p></o:p>
Dim Response As Long<o:p></o:p>
Set Home = ActiveHouse<o:p></o:p>
Set Door = Home.FrontDoor<o:p></o:p>
Set Missus = Home.CurrentOccupant<o:p></o:p>
Set <st1:City w:st="on"><st1:place w:st="on">Mobile</st1:place></st1:City> = WorksheetFunction.Large(PocketContents.Items, 1)<o:p></o:p>
<o:p> </o:p>
Do<o:p></o:p>
    With Door<o:p></o:p>
        If .Locked = True Then<o:p></o:p>
            Do<o:p></o:p>
                Mobile.Dial ("555-1234")<o:p></o:p>
            Loop Until Mobile.Answer.Object = Missus<o:p></o:p>
            Application.Wait (TimeValue(Len(Nagging)))<o:p></o:p>
        End If<o:p></o:p>
    End With<o:p></o:p>
Loop Until Door.Locked = False<o:p></o:p>
Set DishWasher = ActiveDishWasher<o:p></o:p>
If DishWasher.Finished = True Then<o:p></o:p>
    c = DishWasher.Items.Count<o:p></o:p>
    ReDim Dishes(c)<o:p></o:p>
    For i = 1 To c<o:p></o:p>
        Dishes(i) = DishWasher.Items(i)<o:p></o:p>
    Next i<o:p></o:p>
    For i = 1 To c<o:p></o:p>
        With Dishes(i)<o:p></o:p>
            .Remove<o:p></o:p>
            .xlPutAway<o:p></o:p>
        End With<o:p></o:p>
    Next i<o:p></o:p>
End If<o:p></o:p>
With Missus<o:p></o:p>
    Do<o:p></o:p>
        AcceptableTimeToAsk = False<o:p></o:p>
        If Len(Nagging) <= Patience Then<o:p></o:p>
            AcceptableTimeToAsk = True<o:p></o:p>
        End If<o:p></o:p>
    Loop Until AcceptableTimeToAsk = True<o:p></o:p>
End With<o:p></o:p>
Response = Missus.Questions.Ask("Do you know where my car keys are?", vbYesNo)<o:p></o:p>
If reponse = vbNo Then<o:p></o:p>
    CarKeys.Lost = True<o:p></o:p>
    Mobile.Dial(vbTaxi) Destination:= Office<o:p></o:p>
    Exit Home<o:p></o:p>
    Else<o:p></o:p>
        CarKeys.Found = True<o:p></o:p>
End If<o:p></o:p>
End Sub
```


----------



## xlHammer (Aug 18, 2008)

I nominate Lewiy to be the winner.


----------



## Patience (Aug 18, 2008)

Lewiy said:


> Dim Nagging As Long



Priceless! Thank you for making my day.


----------



## RoryA (Aug 18, 2008)

Lewiy said:


> ```
> Dim Missus As Object
> ```


 
You're a brave man to put *that* in writing...


----------



## Lewiy (Aug 18, 2008)

rorya said:


> You're a brave man to put *that* in writing...


 
It's ok, in a previous module I already did:

Dim Lewiy as Brave


----------



## Greg Truby (Aug 18, 2008)

To Lewiy --

Just skimmed it quickly...

You do not need to test booleans for true or false, they ARE true for false, i.e. you can tighten

```
Do
    With Door
        If .Locked = True Then
            Do
                Mobile.Dial ("555-1234")
            Loop Until Mobile.Answer.Object = Missus
            Application.Wait (TimeValue(Len(Nagging)))
        End If
    End With
Loop Until Door.Locked = False
```
Down to:

```
Do While Door.Locked
    Do
        Mobile.Dial ("555-1234")
    Loop Until Mobile.Answer.Object = Missus
    Application.Wait (TimeValue(Nagging))
Loop
```
Also please note that (as Bryony has pointed out) Nagging is a long, not a string so *Len(Nagging)* is always going to return 4.

Any finally, your variable Patience (no relation to Bryony) is ambiguous.  You need to specify _herPatience_ or _myPatience_. If hers then obviously it's:
	
	
	
	
	
	



```
dim herPatience as byte
```
:wink:


----------



## xlHammer (Aug 18, 2008)

I tried pasting into the code window and this line spontaneously appeared;


```
Set Missus.Mood(Today) = Rand()
```
 
and I couldn't delete it?


----------



## kgkev (Aug 18, 2008)

I tried the deleting all reference to "missus" and it deleted half my code in all my macro that I have ever create - I can't understand it.


----------



## Patience (Aug 18, 2008)

xlHammer said:


> I tried pasting into the code window and this line spontaneously appeared;
> 
> 
> ```
> ...




I think it interferes with the PMT function. Excel can't cope with both.


----------



## xlHammer (Aug 18, 2008)

Aargh! It's writing itself, what does this mean?


```
If Missus.Mood(Today) <= Min(Tolerence) then
Shoes(Pairs) = Shoes(Pairs).Count + 1
End if
```


----------



## Lewiy (Aug 18, 2008)

Greg:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Due to the inherent volatility of the Missus object, I have decided to cut this down to a more stable piece of code:


```

```


```

```


```
Sub Find_Keys()<o:p></o:p>
Dim Pockets As Range, Keys As Range<o:p></o:p>
Set Pockets = Range(Jacket.Pockets, Jeans.Pockets)<o:p></o:p>
Set Keys = Location(Keys)<o:p></o:p>
If Not Intersect(Pockets, Keys) Is Nothing Then<o:p></o:p>
    CarKeys.Found = True<o:p></o:p>
    Else<o:p></o:p>
        CarKeys.Found = False<o:p></o:p>
        Mobile.Dial(vbTaxi) Destination:= Office<o:p></o:p>
        Exit Home<o:p></o:p>
End If<o:p></o:p>
End Sub
```


----------



## Greg Truby (Aug 18, 2008)

Patience said:


> I think it interferes with the PMT function. Excel can't cope with both.





xlHammer said:


> ```
> If Missus.Mood(Today) <= Min(Tolerence) then
> ```


Fascinating, I would rather have expected
	
	
	
	
	
	



```
Do Until Missus.Mood(Today) >= Min(Tolerence)...
```



Lewiy said:


> Due to the inherent volatility of the Missus object, I have decided to cut this down to a more stable piece of code...


A most prudent observation & decision, methinks. I definitely think you've improved robustness.


----------



## xyzabc198 (Aug 18, 2008)

=AND(AE1<>"",AE1=TODAY())

Might not be the most pointless conditional format ever...but im pretty new to this


----------



## RoryA (Aug 18, 2008)

If we were in .Net, then we could:

```
Try
    herPatience
Catch It
Finally
   BuyFlowers
End Try
```


----------



## Lewiy (Aug 18, 2008)

xyzabc198 said:


> =AND(AE1<>"",AE1=TODAY())
> 
> Might not be the most pointless conditional format ever...but im pretty new to this


 
Actually, I wouldn’t say that was pointless at all, you are testing that a cell contains today’s date and that it is not blank.  I can think of several reasons why you would want to test this condition.


----------



## Lewiy (Aug 18, 2008)

```
If HerPatience > 1 Then Life = False
```


----------



## kgkev (Aug 18, 2008)

> I think it interferes with the PMT function. Excel can't cope with both.


 
This is just a case of calculating the week number

=if(mod(week(A1),4)=0,"PMT",if(randbetween(1,2)=1,"PMT","SAFE")

I have added an allowance for the unexpected moodswings not sure how accurate this is though.


----------



## MrKowz (Aug 18, 2008)

This has turned into a very epic thread.


----------



## Jonmo1 (Aug 18, 2008)

=MYMOUTH=LOOKUP(PMT,{0,1},{"Open","Shut"})


----------



## xyzabc198 (Aug 18, 2008)

Lewiy said:


> Actually, I wouldn’t say that was pointless at all, you are testing that a cell contains today’s date and that it is not blank. I can think of several reasons why you would want to test this condition.


 
As far as im aware that would check to make sure that it was greater or less than todays date and was equal to today


----------



## Legacy 96851 (Aug 18, 2008)

xyzabc198 said:


> As far as im aware that would check to make sure that it was greater or less than todays date and was equal to today


 
Not sure why you think that, but try it out as a conditional format. It's a duplicate condition, because a cell that is equal to today's date is automatically not blank, and one not blank is automatically not today's date, but it still formats only cells containing the present date.


----------



## xyzabc198 (Aug 19, 2008)

Oh well...like I said, I'm new to this hehe


----------



## kgkev (Aug 20, 2008)

I was asked to make some changes to a spreadsheet today and I found this

=SUM(E5+K5)


----------



## Lewiy (Aug 20, 2008)

kgkev said:


> I was asked to make some changes to a spreadsheet today and I found this
> 
> =SUM(E5+K5)


 
It's not a useless formula in itself (I've seen this done before too), but it is a pretty pointless waste of characters, even =SUM(E5,K5) is pretty wasteful.  But I guess you could keep going:

=MIN(MAX(COUNT(SUMPRODUCT(AVERAGE(SUM(E5+K5))))*(E5+K5)))


----------



## Oaktree (Aug 20, 2008)

Lewiy said:


> even =SUM(E5,K5) is pretty wasteful.



I disagree with this one.  =SUM(E5,K5) is different from =E5+K5, particularly if either E5 or K5 could contain a text value.


----------



## Jonmo1 (Aug 20, 2008)

> I disagree with this one. =SUM(E5,K5) is different from =E5+K5, particularly if either E5 or K5 could contain a text value.


That's very true...

THIS would make it pointless, a + instead of ,

=SUM(E5+K5)


----------



## Greg Truby (Aug 20, 2008)

Oaktree said:


> I disagree with this one. =SUM(E5,K5) is different from =E5+K5, particularly if either E5 or K5 could contain a text value.


 
That's a great point, Matt.  I'd forgotten about SUM's robustness in handling non-numeric variants.  Interesting that SUM(A1,B1) where the cells contain TRUE returns zero, but =A1+B1 returns 2.


----------



## Lewiy (Aug 20, 2008)

Greg Truby said:


> Interesting that SUM(A1,B1) where the cells contain TRUE returns zero, but =A1+B1 returns 2.


 
But =SUM(--A1,--B1) returns 2 as does =SUM(A1+B1)
and
=SUM(A1:B1) returns zero.

Curious! <!-- / message --><!-- sig -->


----------



## Richard Schollar (Aug 20, 2008)

Greg Truby said:


> Interesting that SUM(A1,B1) where the cells contain TRUE returns zero, but =A1+B1 returns 2.



Greg, surely that's what you would expect given that Sum performs no coercive operations on the arguments passed to it?


----------



## Lewiy (Aug 20, 2008)

=A1*B1 returns 1 but
=PRODUCT(A1,B1) returns zero
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I guess this is just how the functions interpret Boolean values as either numbers or not.


----------



## Legacy 96851 (Aug 20, 2008)

It's a bit ironic that this thread has pretty much become "why certain combinations of functions *aren't* pointless."


----------



## Lewiy (Aug 20, 2008)

Yourself said:


> It's a bit ironic that this thread has pretty much become "why certain combinations of functions *aren't* pointless."


 
The best way to prove something is pointless is to disprove that it’s not!


----------



## Jonmo1 (Aug 20, 2008)

I think that is the Scientists Code or something...

It's easier to prove something FALSE, than it is to prove it TRUE.
You only have to find 1 reason for it to be FALSE, but you need to explore ALL possiblities to prove it TRUE..


----------



## Pugster (Aug 20, 2008)

When you eventually decide upon the most pointless combination of functions, they will obviously satisfy the condition of being the most pointless combination of functions and therefore will cease being the most pointless combination of functions.

Hofstatder-esc recursion.


----------



## Lewiy (Aug 20, 2008)

I think we can make an exception for a function’s only purpose being that it is the most pointless function!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>


----------



## xyzabc198 (Aug 20, 2008)

Lewiy said:


> I think we can make an exception for a function’s only purpose being that it is the most pointless function!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>


 
Quite ironic really that the most POINTLESS function is the whole POINT of this conversation!


----------



## Greg Truby (Aug 20, 2008)

RichardSchollar said:


> Greg, surely that's what you would expect given that Sum performs no coercive operations on the arguments passed to it?


 
At least for my train of thought, I'd say you've inverted the flow of the logic there, Richard. I do not _expect_ this because I do not *know* that SUM performs no coersions because I don't have the actual code that's in SUM memorized (can't quite recall where I saw it once upon a time). Rather I would say that given this treatment of booleans one can reasonably conclude that SUM performs no coersions.


----------



## arkusM (Aug 20, 2008)

Greg Truby said:


> At least for my train of thought, I'd say you've inverted the flow of the logic there, Richard. I do not _expect_ this because I do not *know* that SUM performs no coersions because I don't have the actual code that's in SUM memorized (can't quite recall where I saw it once upon a time). Rather I would say that given this treatment of booleans one can reasonably conclude that SUM performs no coersions.


 

Check this link out from John site:
What is truth!
http://spreadsheetpage.com/index.php/oddity/what_is_truth/

It is interesting...


----------



## Richard Schollar (Aug 20, 2008)

Greg Truby said:


> At least for my train of thought, I'd say you've inverted the flow of the logic there, Richard. I do not _expect_ this because I do not *know* that SUM performs no coersions because I don't have the actual code that's in SUM memorized (can't quite recall where I saw it once upon a time). Rather I would say that given this treatment of booleans one can reasonably conclude that SUM performs no coersions.



You're quite right Greg - I was drawing one too many conclusions from incomplete data.  

Of course, had your memory been up to speed (old man ) then you would have remembered all the minutiae of the SUM coding so I wouldn't have had to jump to any conclusions.  So it's your fault really...


----------

