Gruss Forum • View topic

by **knot** » Tue Aug 07, 2007 8:45 pm

please help

Cells A1,A2, or A3, if any TWO of these cells are at a certain number i want cell B1= True

by **GeorgeUK** » Tue Aug 07, 2007 8:57 pm

I HATE USING SUMPRODUCT!!!!!

Aaaah - that's better.

=IF(SUMPRODUCT(--(A1:C1=G1))=2,TRUE,FALSE)
IF A1 to C1 = G1 value (-- convert true and false to 1 & 0) twice, true, else false

Just make sure to use >= if required

by **knot** » Tue Aug 07, 2007 9:11 pm

George thank you. i would like the value to be >1 <1.1. cant get this to work though.

by **GeorgeUK** » Tue Aug 07, 2007 9:31 pm

Nothing but trouble you. :wink:

=IF(SUMPRODUCT(--(A1:C1>1),--(A1:C1<1.1))=2,TRUE,FALSE)

by **knot** » Tue Aug 07, 2007 9:37 pm

Thank you George.

by **knot** » Sun Sep 23, 2007 9:24 pm

I would just like to thank Ronnie for helping with my spreadsheet. I know he has helped some others on here and he has been a massive help to me. The fully automated sheet he has written for me is well above my level, but works very well.

I suffer from a lack of disipline so a automated sheet is the only way for me. Good luck all. :roll:

by **JimGlen** » Wed Sep 26, 2007 4:25 pm

Hi
=IF(SUMPRODUCT(--(A1:C1>1),--(A1:C1<1.1))=2,TRUE,FALSE)

I want to know why there is " -- " in this formula, i searched and cant find any info.
Can you or anyone explain to me.

Jim

by **GeorgeUK** » Wed Sep 26, 2007 5:09 pm

Basically, -- converts True/False Statements to 1 or 0

So helps if you want to count or sum the number of statements that are True etc

by **JimGlen** » Wed Sep 26, 2007 5:42 pm

But im getting TRUE or FALSE returned

by **GeorgeUK** » Wed Sep 26, 2007 7:49 pm

That's because the formula is asking for that to be returned.

If (condition) then TRUE else FALSE

If you want 1 or 0 returned alter the TRUE and FALSE at the end of the formula

To see what a formula is doing, select the cell with the formula in it, click the fx to the right of the formula bar at the top and you should see how the breakdown if the IF formula is being done.
Click another part of the formula and you will see how that part is being calculated and the result of that piece in the calculation.

by **JimGlen** » Wed Sep 26, 2007 8:16 pm

So that goes back to my original question.
What does the "--" mean

by **JimGlen** » Wed Sep 26, 2007 8:22 pm

I see
It converts the query in the argument

--(A1:C1>1) is 1,1,1

I thought you meant it converts the TRUE FALSE :oops:

Worked it out in the end.

Thanks for your help

another excel question

another excel question

Who is online

Sports betting software from Gruss Software