Column 26~ =IF(RC[-6]="","",IF(AND(RC[-14]=1,RIGHT(RC[-1])=5),RC[-9])) produces 3 results, – a) blank cell, b) cell result of 0 (zero), c) FALSE. Problem is I have to identify, separate out, react with, by the row, - are the column cells that FALSE appears in, but not the zero result. I can find no way of doing this that does not confuse the naturally occurring 0 results, which as far as I am concerned have to be a completely independent/different result from FALSE in the column cell. In other words the zero results are wanted or the FALSE results, either way, but any reaction seems to cause FALSE to default to 0 as a value.
OK, what about text string “FALSE”? I mean
=IF(RC[-6]="";"";IF(AND(RC[-14]=1;RIGHT(RC[-1])="5");RC[-9];"FALSE"))
1 Like
Hi, Thanks, I’ve tried entering that and it produces the same result I got with similar formulae; what happens is the cells in the column which have a 0 result are all changed to FALSE as the result of the formula is changed by dragging down and the cells with FALSE remain the same as FALSE.
I naturally thought of making the 0 result different just by adding 2, and another one was If(RC[-1]=0,“A”,"" . Neither work because when RC[-1] is a cell with FALSE in it it shows “A” because FALSE is a 0, the same 2 as everything turns into 2.
What to do, in Calc it really is. And in most cases it is very convenient, makes life easier. In most cases, but not in yours. Try experimenting with the TYPE() function - the formula will get more cumbersome, but you should be able to find a working workaround. (I hope)
1 Like
OK. Will try that, thanks very much for the suggestion.
Have tried the Type() function and got nowhere so far. However I have worked back through previous adjacent columns and found some behavior in the formula in these where #VALUE! is showing in the function wizard but is not affecting the result in the column - i.e. this error is not showing as a result on the spreadsheet. I think my problem is “feeding in” from previous column formulae.
My 3 results that I need to maintain in some way i.e. blank, 0, and FALSE (or some other indicator than FALSE or indeed zero, or both) ; may originate in a previous column result.
The errors caused will be to my lack of understanding the syntax and logic of the code. I will try to sort this.
Yes, there’s no boolean type in Calc. If you need a distinct type for some case, use an error like NA, and detect it using IFERROR or IFNA.
Thanks Mike will try NA.
Have tried many function ideas. What is happening is I am getting 3 results visually from the same formula down a column which is analysing previous columns, the cells resulting in FALSE are the ones I need to analyse further, while the cells showing 0 are just another group to be kept separate.
So visually I can see the three different data types but cannot react with them as the spreadsheet assumes 0=FALSE and visa-versa.
To get so far as this, i.e. and have identified (FALSE) = the critical information I need to go forward with from the data), and not be able to react with it is mind-blowingly frustrating. ~So I can see it but cannot get any further.
I have been looking at other spreadsheets and have only found (so far) Google sheets that provide an analitical functions for obtaining further data from TRUE and FALSE results (if it works). I won’t try it. I cant stand Google, and being showered with rubbish after signing in to it.
Also I’ll only use open-source. So here’s to perseverance.
You never provided any sample, with exact explanation what is the further processing of the result. From you last comment (“cannot react with them as the spreadsheet assumes 0=FALSE and visa-versa”), I do not see what exactly prevented you from reacting on an error returned from a formula like
=IF(RC[-6]="";"";IF(AND(RC[-14]=1;RIGHT(RC[-1])=5);RC[-9];NA()))
A following formula could do something like
=IF(ISNA(RC[-1]);"Third result identical to FALSE";IF(ISTEXT(RC[-1]);"First (empty) result";"Second (numeric) result"))
A clear XY problem.
1 Like
See sample of spreadsheet in table below: - Note this is a new spreadsheet so column references have changed.
Column 25~ =IF(RC[-6]="","",IF(AND(RC[-14]=1,RIGHT(RC[-1])=5),RC[-9])) produces 3 results, – a) blank cell, b) cell result of 0 (zero), c) FALSE. Problem is I have to identify, separate out, react with, the column cells that FALSE appears in but not zero. I can find no way of doing this that does not confuse the naturally occurring 0 results, which as far as I am concerned are a completely different result from FALSE in the column cell.
What is happening is the FALSE result is 0, (as TRUE is 1). This is like quantum mechanics – if you measure it, i.e. cause it, (the FALSE result) to react with a further formula in the next column – it changes to default background value of 0. It doesn’t matter what sort of inquisitive formula you apply, it changes. A bit like Schrodinger’s cat – it exists in a state of limbo until you open the box, then its either dead or alive, but you don’t know until you look !!
[FALSE & ZERO RESULT](file:///home/andyo/Documents/Excerpt%20from%20spreadsheet%20%220-2023-1-AOWN-test-04042023%20(copy)%22%20%2008-06-2023.html)
Table info below is unreadable there see link above
12
13
14
15
16
17
18
19
20
21
22
23
24
25
7.41666666666667
7.08333333333333
6.91666666666667
6.58333333333333
7.5
7.16666666666667
7
6.66666666666667
7
7
7
7.5
0.5
45
45.5
7
0
FALSE
7.58333333333333
7.25
7.08333333333333
6.75
7.66666666666667
7.33333333333333
7.16666666666667
6.83333333333333
7.75
7.41666666666667
7.25
6.91666666666667
7.83333333333333
7.5
7.33333333333333
7
7
7.91666666666667
7.58333333333333
7.41666666666667
7.08333333333333
8
7.66666666666667
7.5
7.16666666666667
8
8
1
7.5
-0.5
48
47.5
7.5
1
0
8.08333333333333
7.75
7.58333333333333
7.25
Ref the table above – I tried to extract the pertinent information, rows, columns, from my spreadsheet, however, for it not to loose its logical links I would have to send at least 60 rows, and 51 columns, otherwise the cells default to #REF!. Alternatively I would have to send the whole sheet.
There is information in the spreadsheet that I do not want to publish at this time.
Column 12, 13, 14, 15 =RC[-4]/12
Column 16~ =IF(TRUNC(RC[-1],0)=RC[-1],RC[-1],IF(TRUNC(RC[-2],0)=RC[-2],RC[-2],IF(TRUNC(RC[-3],0)=RC[-3],RC[-3],IF(TRUNC(RC[-4],0)=RC[-4],RC[-4],""))))
Column 17~ =IF(OR(RC[-11]=“2”,RC[-11]=“4”,RC[-11]=“5”,RC[-11]=“6”,RC[-11]=“8”,RC[-11]=“0”),"",RC[-1])
Column 18~ =IF(RC[-1]="","",IF(TRUNC(RC[-3],0)=RC[-3],RC[-17]-RC[-7],IF(TRUNC(RC[-4],0)=RC[-4],RC[-17]-RC[-8],IF(TRUNC(RC[-5],0)=RC[-5],RC[-17]-RC[-9],IF(TRUNC(RC[-6],0)=RC[-6],RC[-17]-RC[-10],"")))))
Column 19~ =IF(RC[-2]="","",IF(TRUNC(RC[-4],1)=SUM(RC[-3]+0.5),SUM(RC[-3]+0.5),IF(TRUNC(RC[-5],1)=SUM(RC[-3]+0.5),SUM(RC[-3]+0.5),IF(TRUNC(RC[-6],1)=SUM(RC[-3]+0.5),SUM(RC[-3]+0.5),IF(TRUNC(RC[-7],1)=SUM(RC[-3]+0.5),SUM(RC[-3]+0.5),SUM(RC[-3]-0.5))))))
Column 20~ =IF(RC[-3]="","",SUM(RC[-1]-RC[-3]))
Column 21~ =IF(RC[-4]="","",SUM(RC[-16]/2))
Column 22~ =IF(RC[-5]="","",RC[-1]+RC[-2])
Column 23~ =IF(RC[-6]="","",IF(TRUNC(RC[-8],4)=RC[-8],RC[-8],IF(TRUNC(RC[-9],4)=RC[-9],RC[-9],IF(TRUNC(RC[-10],4)=RC[-10],RC[-10],IF(TRUNC(RC[-11],4)=RC[-11],RC[-11],"")))))
Column 24~ =IF(RC[-7]="","",DELTA(RC[-1],RC[-5]))
Column 25~ ==IF(RC[-8]="","",IF(AND(RC[-1]=1,RIGHT(RC[-3],1)=5),RC[-2]))
Ref your suggested formulae, (thanks by the way)
=IF(RC[-6]="";"";IF(AND(RC[-14]=1;RIGHT(RC[-1])=5);RC[-9];NA())) pasted into column 25 for NA() and
=IF(ISNA(RC[-1]);“Third result identical to FALSE”;IF(ISTEXT(RC[-1]);“First (empty) result”;“Second (numeric) result”)) pasted into column 26
I’m afraid the same thing happens. NA() is identified in 0 and FALSE cell results and so only “Third result identical to FALSE”; is shown corresponding to these cells.
Visually
The result FALSE or 0 must be caused by the code that controls TRUE or FALSE. The question is – why does the formula produce a 0 in one instant and FALSE in another, when I can (so far) discern no difference in the information referenced by the functions in the formula from one row to the next?
Andy
Hi - the objective of interaction with the results of formula in col.25 is to count FALSE results, Count 0 results, and then List each Set separately.
Sorry if I’ve cluttered up things. I don’t know how to do a lot of things. No need to shout, I apologise for being annoying.
Hi Mike. Can communicate with you privately by email?
Yes, you may post a message to mikekaganski@hotmail.com; or you may send a PM right here, in the Ask, clicking on my name, then using the Message button in the opened popup.
Probable cause
The part of your formula RIGHT(RC[-1]) will always evaluate to a single character (so a “string” data type), regardless of whether cell content in the previous column is numeric or character. You are comparing the string to the number 5. This will always return FALSE.
Solution
Be consistent with data types. Either make left side of the compare numeric, or compare to the string constant “5”. A couple of approaches, off the cuff (use only one 
):
=IF(RC[-6]="","",IF(AND(RC[-14]=1,VALUE(RIGHT(RC[-1]))=5),RC[-9]))
=IF(RC[-6]="","",IF(AND(RC[-14]=1,RIGHT(RC[-1])="5"),RC[-9]))
=IF(RC[-6]="","",IF(AND(RC[-14]=1,MOD(RC[-1],10)=5),RC[-9]))
For numeric data I guess the bottom one is the most “efficient” (only noticeable with very large datasets) but it probably only makes sense for integer values.
If none of those is working for you, there may be other issues with your data. Numeric strings with leading/trailing spaces are frequent culprits.
Ok. thanks for suggestions, will try each one of these.
I may have misread your question. Sorry about that!
I thought you had a problem with too many FALSE returns, but upon second reading it looks like you just need to distinguish the zero return from the “test fail”, both of which appear as a zero value.
For that, using the string “FALSE” (or some other text) as an explicit “else” value - as initially suggested by @JohnSUN - makes more sense. In my opinion, @mikekaganski’s approach - using an error indicator to flag the result - is cleaner (more “type safe”, if you will), but it will make error values appear, which some users find undesirable.
=IF(RC[-6]="","",IF(AND(RC[-14]=1,RIGHT(RC[-1])="5"),RC[-9],"FALSE")
Suggestion 1) - produces result of 0 where there was previously a 0 and previously a FALSE. This means I can no longer differentiate between the two result to count them separately.
Suggestion 2) - produces result of 0 or a number indiscriminately instead of 0 or FALSE, making it impossible to count results which were previously 0 or FALSE as two sets of info.
Suggestion 3) - produces result of 0 for all previous FALSE and 0 results