[Solved] Preventing STYLE() from returning 0?

Discuss the spreadsheet application
Post Reply
ba301
Posts: 5
Joined: Wed Jun 09, 2021 6:57 pm

[Solved] Preventing STYLE() from returning 0?

Post by ba301 »

Hello, I am trying to figure out how to either get STYLE() to NOT return a 0, how to hide it, or another function that will accomplish the same task I desire that does not include printing a 0.

Currently I am using this formula inside of a cell:

Code: Select all

=IF(SUM(J5:J65)=1;STYLE("yellowhl");STYLE("default")) &""& IF(SUM(J5:J65)=1;"Behind";"")
... with the purpose of being a flag that will only appear in a cell next to a clients name that is behind on their payments. I have set up the rest of the formulae in the sheet to correctly send the information to this cell on a monthly basis, but my problem is a very annoying cosmetic one: Instead of printing out a "Behind" in a yellow background, it is printing out "0Behind" on a yellow background.

So while I have the sheet doing what I want it to do essentially, its putting this very annoy 0 that appears to be a text zero right next to the "Behind", apparently because STYLE() always returns a 0.

Ive tried having the global setting calc - view - zero values set to off (does not work in this instance but applies to the rest of the sheet), Ive tried eliminating leading zeroes (does not work in this instance), and Ive tried embedding the above formula in another IF() statement and telling it that if "0Behind" is true, post "Behind" instead (returns a #VALUE error), and have now run out of ideas about how to get this superfluous 0 out of the cell.

Any advice would be helpful on how to either prevent STYLE() from returning a 0, or how else I can accomplish my goal of BOTH highlighting the cell yellow, and also posting the short text "Behind" with it without extraneous text included. I feel that there must be a simple solution to this, but do not have the expertise in spreadsheets to know what it is, and its proving to be very hard trying to obtain the answer through websearching (discovered some interesting things about AOO by trying, but have not yet found my solution).

Thank you in advance!
Last edited by Hagar Delest on Wed Jun 09, 2021 8:44 pm, edited 1 time in total.
Reason: tagged solved.
OpenOffice 4.1.7 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Preventing STYLE() from returning 0?

Post by Villeroy »

Sum returns a number whereas & concatenates text. Just add the 0 to the sum.

Oh, you wanted to return text.
Last edited by Villeroy on Wed Jun 09, 2021 8:19 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Preventing STYLE() from returning 0?

Post by karolus »

SUM(J5:J65) is equal 1 or not, … there is no reason to check twice.

Code: Select all

=IF(  SUM(J5:J65)=1 ; "Behind" & T(STYLE( "yellowhl" ) ) ; "" & T(STYLE( "default" ) ) )
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
ba301
Posts: 5
Joined: Wed Jun 09, 2021 6:57 pm

Re: Preventing STYLE() from returning 0?

Post by ba301 »

Villeroy wrote:Sum returns a number whereas & concatenates text. Just add the 0 to the sum.
Unfortunately Im unsure about how to apply this solution.
karolus wrote:SUM(J5:J65) is equal 1 or not, … there is no reason to check twice.

Code: Select all

=IF(  SUM(J5:J65)=1 ; "Behind" & T(STYLE( "yellowhl" ) ) ; "" & T(STYLE( "default" ) ) )
Thank you karolus, that seems to work perfectly!

Ive seen "&T" in other posts while searching, and that seems to be the solution here. But could someone give me a solid explanation about what precisely it does and how best to use it? How is it able to properly exclude the 0? The AOO help does not seem to have an entry for this!
Ahh, its in the help just under "T".
OpenOffice 4.1.7 on Windows 10
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] Preventing STYLE() from returning 0?

Post by MrProgrammer »

ba301 wrote:Currently I am using this formula inside of a cell:=IF(SUM(J5:J65)=1;STYLE("yellowhl");STYLE("default")) &""& IF(SUM(J5:J65)=1;"Behind";"") ... with the purpose of being a flag that will only appear in a cell next to a clients name that is behind on their payments.
Some solutions, demonstrated in the attachment:
=IF(SUM(J5:J65)=1;IF(STYLE("yellowhl");;"Behind");IF(STYLE("");;""))
=IF(STYLE(REPT("yellowhl";SUM(J5:J65)=1));;REPT("Behind";SUM(J5:J65)=1))
=IF(STYLE(REPT("yellowhl";Z1);;REPT("Behind";Z1) after putting =SUM(J5:J65)=1 in unused cell Z1
202106091839.ods
(11.98 KiB) Downloaded 96 times
Useful Calc idioms:
• IF(STYLE(α);;β)   The function returns β with style α applied.
                    Evaluating STYLE sets the style.  When α is "", the style is cleared.
                    STYLE always returns 0 (false) so the second operand of IF isn't needed.
• REPT(α;β)         The function returns text α if test β is true, otherwise it returns "".
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply