If function

[dci1966]

Please can you help an excel novice with a question on how to write a formula using the IF function to detect certain text in a column of a spreadsheet and then to turn numbers in three other columns into negatives if that text is true? Thank you

 

[Joe4]

Welcome to the Board!

Let's say that you are looking for the word "dog" in A1, and if it exists, take the negative value of the entry in cell B1.
Then that formula would look like this:

=IF(ISNUMBER(FIND("dog",A1)),0-B1,B1)

Is that what you are after?

 

[Yongle]

Welcome to the board dci1966

What is in the other 3 columns?
- values?
- formula?

 

[dci1966]

Welcome to the Board!

Let's say that you are looking for the word "dog" in A1, and if it exists, take the negative value of the entry in cell B1.
Then that formula would look like this:

=IF(ISNUMBER(FIND("dog",A1)),0-B1,B1)

Is that what you are after?

Sorry, maybe I should have explained it better! I actually have 3 columns of numbers which I have extracted from an accounts package but the credits haven't come through as negatives so they are throwing off my totals - I want to look for all the credit notes (marked "CRN" in column A) and then turn the positive numbers in columns C,D and E to negative, is that even possible?!

 

[Joe4]

Sorry, maybe I should have explained it better! I actually have 3 columns of numbers which I have extracted from an accounts package but the credits haven't come through as negatives so they are throwing off my totals - I want to look for all the credit notes (marked "CRN" in column A) and then turn the positive numbers in columns C,D and E to negative, is that even possible?!

Any cell can either have a hard-coded value, or a formula, but not both at the same time.
So to change existing hard-coded values in cells, that will require VBA. Is that something you are willing to use?
If so, are the cells EXACTLY equal to "CRN", or is part of a larger string?

 

[dci1966]

Welcome to the board dci1966

What is in the other 3 columns?
- values?
- formula?

It is hard data extracted from an accounts program, no formulas

 

[Joe4]

If your values in column A were exactly equal to "CRN", here is VBA code that would do what you want:

Sub MyNumFix()

    Dim lr As Long
    Dim r As Long
    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows of data
    For r = 1 To lr
'       Check to see if column A equals "CRN"
        If Cells(r, "A") = "CRN" Then
'           Negate values in columns C-E
            For c = 3 To 5
                Cells(r, c) = 0 - Cells(r, c)
            Next c
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[dci1966]

Any cell can either have a hard-coded value, or a formula, but not both at the same time.
So to change existing hard-coded values in cells, that will require VBA. Is that something you are willing to use?
If so, are the cells EXACTLY equal to "CRN", or is part of a larger string?

LOL definitely not up to VBA!!!!!! cells are exactly CRN or INV..........

 

[Joe4]

LOL definitely not up to VBA!!!!!!

You don't need to understand it or know how to write it in order to use it.
Follow these instructions to insert and run the VBA code that I wrote for you: https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

The only way I can think of to do it without VBA is to set-up three formulas (like the ones I posted in my original response), then do a Copy -> Paste Special Values to copy the results of those formulas over the original data. Then you can delete the formulas and are left with what you want.