I am working on a sheet that helps check my account activities.
How can I make Column “G7(NIP)” display 53.75 if value in Column “E7(Debit)” is greater than or equal to 50000; display 16.13 if less than 50000, or display “0” if empty?
Thanks in anticipation and I hope I am clear enough…
And what to display for 50000 (of “0” is for empty)? or is the title more correct, saying “else 0”, meaning 50000 must result in display of 0?
try ‘nested ifs’, like ‘=IF(B3="";0;IF(B3>50000;53,75;IF(B3<50000;16,13;“undefined”)))’,
german locale, you’ll need ‘.’ instead of ‘,’ and ‘,’ instead of ‘;’,
observe the changed order - hierarchy for the if’s - needed to avoid empty cells being interpreted as ‘0’ and thus catched by the range <50.000,
and for 50.000 you’ll get ‘else-else-else’ and thus ‘undefined’,
if you want a value for 50.000 replace ‘undefined’, or - if 50.000 belongs to one of the ranges - try ‘>=’ for ‘>’ or ‘<=’ instead of ‘<’, or you may shorten the formula to two if’s,
be careful regarding ‘entered values’ vs. ‘calculation results’ in col. E, ‘empty’ or “” is different to ‘0’, even if the 0 isn’t shown reg. the setting of ‘display - zero values’,
or - in other words - be aware that ‘0’ as well as all negative values belong to the range ‘<50000’,
ok?
I’ll edit the question.
It is “if E7>=50000”
50000 and above attracts 53.75 while E7<50000 attracts 16.13.
Thanks
Next time, please keep the question text short so that it doesn’t clog the questions overview.
But what about when the value equals to 50000?
Possible solutions (with English functon names):
=IF(ISBLANK(E7);0;IF(E7>50000;53.75;16.13))
or
=IF(ISBLANK(E7);0;IF(E7>=50000;53.75;16.13))
or
=IF(ISBLANK(E7);0;IF(E7>50000;53.75;IF(E7<50000;16.13;"precisely 50000")))
Edited:
My bad it is “if E7>=50000”
50000 and above attracts 53.75 while E7<50000 attracts 16.13.
Thanks.
I’ll follow your solutions and give you feedback when I get home.
Thanks
“=IF(ISBLANK(E7);0;IF(E7>=50000;53.75;16.13))”
This did it.
Thank you all, You guys rock.
Nested IF’s work fine, thus IF(x<10,—,IF(x<15,—,IF(X<20,—,"")))
But I’d advise being careful about testing for null strings ("") which are not the same as blank cells. The best way I’ve found for testing both conditions is ISNA(REGEX(cell,"$^", with no replacement string). If the regular expression “$^” evaluates to “false” (i.e. end of line not immediately after start of line) and no replacement string is specified, REGEX returns “true”.
DavidL
Yes, that is an another case… But the original questions was: “…or display “0” if empty?” A cell what contains a formula with a nullstring result, or contain a nullstring constant: is not empty.
And it is a main reason, why it is better to upload a real ODF type sample file here instead of a picture of it. Then we can see if the numbers are constants or some result of a formula.
I have asked before, how can I upload ODF type file when asking a question?
There is an “Attachment” icon near the “Image” icon in the header of the “Add details” box:
(See the attached image in my edited answer)
I’m not sure exactly sure what you’re asking. The syntax
=IF(condition1,action1,IF(condition2,action2,…IF(condition,action,finalAction)))
is quite general.
Does IF(ISNA(REGEX(cell,"^$")),IF(cell>= 50000,53.75,IF(cell<50000,16.13,0)),0) do what you need?
The rather complicated looking conditional at the start treats both null strings and blanks as empty, which is an advantage because they look the same on the spreadsheet. ISBLANK() will consider null strings as not-blank.
Not only the nullstring seems as empty. The strings yontaining some spaces: " "; " "; " " - seems empty too.
And sometimes the (conditionally) formatted cells (White characters on a white background) seems empty too.
The the condition in the question was “if empty”.
Zizi64 wrote "Not only the nullstring seems as empty. The strings yontaining some spaces: " "; " “; " " - seems empty too.”
True, and
=IF(ISNA(REGEX(B2,"^\s*$")),IF(B2>= 50000,53.75,IF(B2<50000,16.13,0)),0)
should return zero for cells which are blank, contain null strings, or contain whitespace characters.
Ooops, I had tried to write more than one white spaces between the quote marks, but this page not allow the double, triple spaces in a normal text… I now trying it as a code:
" "; " "; " "I’ve just noticed a space has crept into the formula above: “B2> 50000” should have no space.