Hi
How would I write this in excel to get an answer?

=if(a1=a, and b1=a, and c1=a,"true","fa-
lse")
Hope this makes sense
Thanks in advance

tuiboy1 - 2021-05-27 13:31:00

=IF( AND(A1="a", B1="a", C1="a"), "true","false&q-
uot;)

double quote after false, that's the message board messing things up

Edited by king1 at 1:52 pm, Thu 27 May

king1 - 2021-05-27 13:50:00

Cheers, will give it a go

tuiboy1 - 2021-05-27 14:23:00

Nice. Been having fun playing with modifying the formula. Works well for many situations. I must say I like fiddling around with excel formulas. Can't/don't do graphs and other 'fancy' stuff more because I'm not that interested but I like what can be achieved with formulas.

ETA:

Must point out king1 that your formula specifies that cels a1,b1,c1 must have an exact match for "a" (the letter a in actuality as you have it in quotes) . However tuiboy's original question want's the b1,c1 cells to simply match what is IN cell a - (they also stated a1=a but that's a given anyway).

Edited by nice_lady at 2:55 pm, Thu 27 May

nice_lady - 2021-05-27 14:49:00

i figured the 'a' must have been a value since to be a cell reference it needs a column/row format eg a1... although I suppose it could be a cell name, albeit a slightly unimaginative one, better off just doing an absolute reference eg $A$9

Edited by king1 at 3:01 pm, Thu 27 May

king1 - 2021-05-27 14:58:00

king1 wrote:

i figured the 'a' must have been a value since to be a cell reference it needs a column/row format eg a1... although I suppose it could be a cell name, albeit a slightly unimaginative one, better off just doing an absolute reference eg $A$9

Well I just thought I'd mention it in cast tuiboy got results they didn't want lol.......

nice_lady - 2021-05-27 15:08:00

all good

king1 - 2021-05-27 15:13:00

Cheers Guys, King1 was correct in what I wanted. And it worked.
Awesome, Thanks again

tuiboy1 - 2021-05-28 10:51:00

Another question
I'm not sure of the terminology but can the above formula be nested with another formula?
=IF( AND(A1="a", B1="a", C1="a"), "true","false&q-
uot;,=IF( AND(A1="a", B1="a", C1="B"), "true","false&q-
uot;))

tuiboy1 - 2021-05-28 11:42:00

C1="A" changes to C1="B"

tuiboy1 - 2021-05-28 11:49:00

the if has three parameters
the comparison logic that equates to true or false
what to do if true
what to do if false

they can be nested but it gets messy if you have too many, normally you might place an if statement into the true or false parameter...
https://support.microsoft.com/en-us/office/if-function-%E2%8
0%93-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44b
a-aeb5-4ef99da241c8

looking at your formula, you have the nested if in a non existent 4th parameter, not sure but maybe what you trying to do is
=AND ( IF (*,*,*), IF (*,*,*) )

which is two complete independent IFs nested in an AND - which doesn't really make much sense, I think all you will get is a true or false in that cell

Could be you mean - if true, calculate next if etc
IF (*, IF (*, IF (*,*,*),*) ,*)

We probably need to know more about the context

Edited by king1 at 12:20 pm, Fri 28 May

king1 - 2021-05-28 12:13:00

Thanks for that. Think I might have written it wrong.
Maybe this will make more sense
If A=1 and B=1 and C=1 then ??????
But if
A=2 and B=2 and C=2 then ??????
But if
A=2 and B=2 and C=2 then ?????

and so on
Very Confusing LOL

tuiboy1 - 2021-05-28 12:59:00

That last row meant to be
A=3 and B=3 and C=3 then ?????
etc

tuiboy1 - 2021-05-28 13:00:00

I take it these are mutually exclusive, ie only one of the IFs will ever be true at a time?

king1 - 2021-05-28 13:19:00

Yep

tuiboy1 - 2021-05-28 13:25:00

note i've used the proper cell references (ie the A1), basically the first two FALSE parameters are replaced with another IF

=IF(AND(A1=1,B1=1,C1=1),"-
Row 1",IF(AND(A2=1,B2=1,C2=1)-
,"Row 2",IF(AND(A3=1,B3=1,C3=1)-
,"Row 3",FALSE)))

so if you place 1 and 0 in
A1,A2,A3,B1,B2,B3,C1,C2,C3

whichever row has all 1's will be the Row # displayed - remove the dash at the end of each row, messageboard does that. Is this what you had in mind?

Edited by king1 at 1:42 pm, Fri 28 May

king1 - 2021-05-28 13:41:00

Sorry, i should have put the cell reference
does this look better?
=IF(AND(A1=2,B1=2,C1=2),"-
TRUE",F1),
IF(And(A1=3,B1=3,C1=3,"TR-
UE",F1),
IF(AND(A1=4,B1=4,C1=4),"T-
RUE",F1)))

Don't worry if its to confusing

tuiboy1 - 2021-05-28 14:24:00

take the dash out

tuiboy1 - 2021-05-28 14:25:00

that is showing three IF statements separated by commas, which is invalid
What you are creating is one formula, the output of which is placed in the cell where the formula resides.
F1 is presumably where you want the output to be placed, so the formula would need to be in cell F1, excel can't place a value 'over there' in another cell ...
So two things,
- each of the subsequent IF statements need to go in the previous IF statements FALSE parameter, where you have F1.
- The whole formula should be in cell F1, if that is where you want the output to be.

To test it I would also change your "TRUE" to "TRUE1","TRUE2&-
quot;,"TRUE3" etc so you can see which IF is firing

Edited by king1 at 3:16 pm, Fri 28 May

king1 - 2021-05-28 15:14:00

F1 is equal to A1 plus B1
IE:
A1 =2
B1 =2
therefore
F1= 4
C1 is a different random number, but if it happens to be the same number (although random) as A1 and B1, in this case 2, then I need to somehow record this as true.
If it is not the same number as A1 and B1 then I will stick with F1, Being A1 plus B1. ie 4

tuiboy1 - 2021-05-28 15:35:00

oh ok thats a bit different to what i was thinking.

try this in F1
=IF(AND(C1=A1,C1=B1),C1,SUM(A1-
:B1))

first compares C1 to A1 and B1 - if A1/B1/C1 all the same, outputs C1 as true, otherwise outputs the sum of A1 and B1 as false

Edited by king1 at 3:51 pm, Fri 28 May

king1 - 2021-05-28 15:49:00

LOL nope, its too confusing, so don't worry about it unless you want to keep trying....
F1 is the sum of A1 and B1.
C1 is a random number
In another cell- say G1 i want it to come up with either the word "True" if A1 B1 and C1 are the same number. If they are not i want it to come up as what ever is in F1 ........
I want G1 to do this for all numbers (nested)
IE:
A1 =1
B1 =1
C1 =1
F1 =2
G1 = TRUE

A1 =2
B1=2
C1=2
F1=4
G1=TRUE
An so on

tuiboy1 - 2021-05-28 16:25:00

halfway there then
F1 needs to be
=SUM(A1:B1)

G1 needs to be
=IF(AND(C1=A1,C1=B1),TRUE,F1)

only thing i'm not sure of is
"I want G1 to do this for all numbers (nested)"

Do you mean all Rows? or what?

Edited by king1 at 5:01 pm, Fri 28 May

king1 - 2021-05-28 16:59:00

what exactly is being nested? what is this a spreadsheet of ? context is everything... I'm happy to keep going but it will take an awful long time if it's an hour between posts...

Edited by king1 at 5:13 pm, Fri 28 May

king1 - 2021-05-28 17:09:00

I think thats it, kind of
I was putting
F1 as
=a1+b1
G1 as
=if(and(a1=1,b1=1,c1=1),"-
true",F1)
the way I'm doing it only gives the answer i want if i put the number 1 in A1 and B1. Your way works for all numbers. I want it to do what your way is doing it but I need it to be done my way as there are going to be variables further on in the spread sheet. (I haven't got that far yet LOL)

tuiboy1 - 2021-05-28 17:32:00

it might get a bit messy... what do these numbers go up to? The whole point of spreadsheets is to eliminate the need for hardcoding in numbers like you are wanting.

king1 - 2021-05-28 17:40:00

so what you're looking for nested is
=IF(AND(A1=1,B1=1,C1=1),TRUE,I-
F(AND(A1=2,B1=2,C1=2),TRUE,IF(-
AND(A1=3,B1=3,C1=3),TRUE,F1)))-

This is up to 3, you will need to...
copy the last IF block (count the opening and closing brackets),
then PASTE it back into the formula replacing *F1* towards the end
then change A1=3,B1=3,C1=3 to A1=4,B1=4,C1=4

Hopefully you don't need to go to 99, there are limits... can't see any easier way to do it

Edited by king1 at 5:55 pm, Fri 28 May

king1 - 2021-05-28 17:54:00

Fantastic, will give it a go.
Cheers

tuiboy1 - 2021-05-28 18:21:00

Yep that works. Thanks alot.
Have a good weekend.......Ill have more questions on Monday LOL

Cheers

tuiboy1 - 2021-05-28 18:30:00

One tip; if I have a complex expression, I build it up on a new sheet or add extra columns so that I can 'show working'.

So I might have a column that shows the first part, another column that shows the second part and a third that has the combined result. Once I know the formula is correct and working, then I can hide columns or just combine the result into one nested expression.

Also ' trace precedents' in Excel is useful for expressions as most common mistakes I find are just referencing the wrong cells

gblack - 2021-05-29 09:17:00

Hi again, hope you had a good weekend.
I have completed all my formulas in separate cells. Am I able to "nest" them in one cell. There is a total of 11 cells and about 60 IFs in total.
I When I cut a formula from one cell, excel does not let me paste it it another cell that has a formula in it.
Individual cells work as I wanted but I cant seem to cut and paste to make one big formula.
Hope this make sense.

tuiboy1 - 2021-06-01 21:40:00

when merging formulas you need to make sure you are selecting all the text inside the cell as if you are editing it, you can't just copy the cell and paste into a formula in a different cell (different type of object)

basically replace every reference to that cell with the formula from the other cell. It gets messy though, you need to remove the leading = and they don't always work as expected without modification.

There are also limits to the depth of nested IFs, later versions I believe is 64. You should be probably looking at using a lookup table if you're getting anywhere near that level.

Another option is just to hide the non-essential row/column

king1 - 2021-06-02 08:59:00