Ask Your Question
0

How can I do "if cell value >= 50000 then 53.75, if cell value < 50000 then 16.13, else 0"

asked 2020-09-02 03:04:45 +0200

desiya gravatar image

updated 2020-09-02 15:45:20 +0200

I am working on a sheet that helps check my account activities. image description

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.........

edit retag flag offensive close merge delete

Comments

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?

Mike Kaganski gravatar imageMike Kaganski ( 2020-09-02 06:45:42 +0200 )edit

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 ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-09-02 07:22:36 +0200 )edit

I'll edit the question.

It is "if E7>=50000"

50000 and above attracts 53.75 while E7<50000 attracts 16.13.

Thanks

desiya gravatar imagedesiya ( 2020-09-02 15:36:26 +0200 )edit

2 Answers

Sort by » oldest newest most voted
3

answered 2020-09-02 07:31:59 +0200

Zizi64 gravatar image

updated 2020-09-04 22:44:47 +0200

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:

image description

edit flag offensive delete link more

Comments

My bad it is "if E7>=50000"

50000 and above attracts 53.75 while E7<50000 attracts 16.13.

Thanks.

desiya gravatar imagedesiya ( 2020-09-02 15:35:51 +0200 )edit

I'll follow your solutions and give you feedback when I get home.

Thanks

desiya gravatar imagedesiya ( 2020-09-02 15:39:07 +0200 )edit

"=IF(ISBLANK(E7);0;IF(E7>=50000;53.75;16.13))"

This did it.

Thank you all, You guys rock.

desiya gravatar imagedesiya ( 2020-09-02 23:16:57 +0200 )edit
1

answered 2020-09-03 08:04:11 +0200

DavidL1 gravatar image

updated 2020-09-16 14:40:59 +0200

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

edit flag offensive delete link more

Comments

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.

Zizi64 gravatar imageZizi64 ( 2020-09-03 08:42:04 +0200 )edit

I have asked before, how can I upload ODF type file when asking a question?

desiya gravatar imagedesiya ( 2020-09-04 21:59:44 +0200 )edit

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)

Zizi64 gravatar imageZizi64 ( 2020-09-04 22:43:21 +0200 )edit

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.

DavidL1 gravatar imageDavidL1 ( 2020-09-07 03:23:41 +0200 )edit

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 gravatar imageZizi64 ( 2020-09-07 07:22:09 +0200 )edit

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.

DavidL1 gravatar imageDavidL1 ( 2020-09-16 14:32:47 +0200 )edit

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:

" "; "   "; "       "
Zizi64 gravatar imageZizi64 ( 2020-09-16 15:06:21 +0200 )edit

I've just noticed a space has crept into the formula above: "B2> 50000" should have no space.

DavidL1 gravatar imageDavidL1 ( 2020-09-17 01:36:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-02 03:04:45 +0200

Seen: 419 times

Last updated: Sep 16