Ask Your Question
1

Do math only if the cell has particular color? [closed]

asked 2020-08-13 17:53:20 +0200

multifrag gravatar image

updated 2020-09-26 11:52:37 +0200

Alex Kemp gravatar image

Hello,

I would like to know if it is possible to have a formula similar to this :

=IF(A1=#FF0000,F1/E1,"")

If A1 = Red, do the math, otherwise the cell is empty. I've tried scouring the forum for an answer and couldn't find anything similar.

Thank you

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by igorlius
close date 2020-09-06 19:03:47.749393

2 Answers

Sort by » oldest newest most voted
3

answered 2020-08-14 21:56:50 +0200

igorlius gravatar image

updated 2020-08-30 22:27:19 +0200

Hello,

the following macro function (see end of this post) does what you want. Just put it into Tools -> Macros -> "Edit Macros" and you can call it from inside any cell via

=IFCOLOR("A2","ff0000", A2/A3,-1)
  • The 1st argument is the cell to check quoted as a string
  • The 2nd argument is a hexadecimal color value quoted as a string To view or set the hex value of a cell open the color picker ref1. ref2..
  • the 3rd argument is the result to set when the background color matches
  • the 4th argument is the result to set when the background color deos not match

Example Screenshot:
image description

Here the macro function you have to import:

Function IFCOLOR(cellName As String, hexColor As String, ifValue as variant, elseValue as variant)

    REM remove trailing spaces 
    cellName = Trim(cellName)
    hexColor = UCase(Trim(hexColor))

    Dim decColor As Long

    REM check length
    if (Len(hexColor) <> 6) then
        SUMIFCOLOR = "ERR: invalid hex code, length <> 6"
        Exit Function
    endif

    REM check chars
    For i = 1 To Len(hexColor)
        c = Mid(hexColor, i, 1) 
        Select Case c
            Case "0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F"
            Case Else
            SUMIFCOLOR = "ERR: invalid hex code, invalid hex char"
            Exit Function
        End Select
    Next

    REM convert to long
    decColor = CLng("&H" & hexColor)  REM convert to long

    REM sum result 
    sum = 0.0

    Set oSheet = ThisComponent.CurrentController.ActiveSheet
    Set oCell = oSheet.getCellRangeByName(cellName)

    if(oCell.CellBackColor = decColor) then
        IFCOLOR = ifValue
    else
        IFCOLOR = elseValue
    endif

End Function

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

edit flag offensive delete link more

Comments

1

nice work :-)

the OP request may be questionable ... don't get used to use such things ... but as it came up ... having a solution is fine,

(deccolor is a cruel attribute, hex would be easier)

newbie-02 gravatar imagenewbie-02 ( 2020-08-20 12:09:38 +0200 )edit
1

The hex value can be converted using the DECIMAL() function, like DECIMAL("C9211E";16)

erAck gravatar imageerAck ( 2020-08-20 13:51:45 +0200 )edit

Nice. I did not know about the DECIMAL() function. I'll update the answer to make it more userfriendly. In the end ii went with the the HEX2DEC function, becasue one less argument. - In my personal opinion calling createUnoService for one function ... make the whole solution a bit bloated but .... as long as it works.

igorlius gravatar imageigorlius ( 2020-08-20 14:40:01 +0200 )edit
1

Just as a side note, HEX2DEC() does not accept an optional "0x..." prefix which DECIMAL(...;16) does; I meant that rather as a user-provided argument conversion than being part of the macro function.

However, the bloated callFunction() call is unnecessary as BASIC has a CLng() function to convert a hex string if it is prefixed with "&H", so this instead of ofa.callFunction() should work:

decColor = CLng("&H" & hexColor)

(untested in the context of the macro function).

erAck gravatar imageerAck ( 2020-08-20 19:27:21 +0200 )edit

I keep learning new stuff. Thanks. I'll test your suggestion and update the snippet when it works.

[Update 1] This looks much nicer with the CLng() function. I also added some crude checks to give quick error feedback. I might add some checks for the range argument later. Improvement suggestions are very much welcome.

[Update 2] i changed the macro a bit to make it a more direct answer for the question. It now has a very similar call syntax to the one requested in the question. Also i moved the previous macro function here https://ask.libreoffice.org/en/questi..., because the question is a better fit for it.

igorlius gravatar imageigorlius ( 2020-08-20 19:47:28 +0200 )edit
1

answered 2020-08-13 18:05:03 +0200

Opaque gravatar image

updated 2020-08-13 18:11:02 +0200

Hello,

no - this is not possible with standard functionality. All calculations are done using values of cells and not with attributes of cells (and the background / text color is an attribute of a cell).

PS I'm purposely not talking about user code (macros) and defining a function, which might provide values(!) of attributes. And I can't resist to say, that the idea itself is questionable. If you format a cell a certain way, I'd assume you have some condition to do so. Hence try to use the same condition, which forces the formatting, in your IF statement.

edit flag offensive delete link more

Comments

Thank you for the answer

multifrag gravatar imagemultifrag ( 2020-08-13 18:07:40 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2020-08-13 17:53:20 +0200

Seen: 381 times

Last updated: Aug 30 '20