Subtract from X formula?

tuix · #1 06-24-2019, 11:08 AM

Hello everyone, I am in absolute emergency mode right now, freaking out because I think I messed up big time. The issue is that someone sent me data with survey answers ranging between values 1-5. I put them in an excel sheet to make tables. The issue is, in order for the answers to make sense, they needed to be reversed. However I did not know this, and therefore made a whole excel file of 12 sheets and near 200 tables according these values. Now she tells me that they needed to be reversed.

What I need to know is whether there is a way to convert all cell values into what they become after being subtracted from 6. Example;

Currently the cells are:
A1 = 2
A2 = 4.2
A3 = 3.51

What I need them to be:

A1 = 4
A2 = 1.8
A3 = 2.49

Is there a way to do this? I looked around a bit and fiddled with excel on my own but couldn't figure it out. Things like =6-X obviously don't work. This thing is due in a few days so I would really appreciate to know even if there is no way of doing this so I start redoing the data right now. Thanks for all the help.

Alansidman · #2 06-24-2019, 03:15 PM

I would do this with VBA. The following code will work if you have all your data in Column A.

Code:

Option Explicit

Sub MinusSix()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr    'Assumes data starts in Row 1
        Range("A" & i) = 6 - Range("A" & i)
    Next i
    Application.ScreenUpdating = True

End Sub

Alansidman · #3 06-24-2019, 03:21 PM

I would do this with VBA. The following code will work if you have all your data in Column A.

Code:

Option Explicit

Sub MinusSix()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr    'Assumes data starts in Row 1
        Range("A" & i) = 6 - Range("A" & i)
    Next i
    Application.ScreenUpdating = True

End Sub

ArviLaanemets · #4 06-25-2019, 12:03 AM

1. Into row 1 of some free column enter the formula

Code:

=6-$A1

2. Copy the formula down until row with last entry in column A;
3. Copy all cells with formula. Right-click on cell A1, and select Paste Special and then the option Values from dropdown menu;
4. Delete formulas. It's done!

p45cal · #5 06-27-2019, 06:03 PM

Another way: Create a new sheet and call it bleugh. In its cell A1 enter 6, and in cell B1 enter -1

Then go to your sheet with the cells that want changing, select the cells and run:

Code:

Sub blah()
    Selection.SpecialCells(xlCellTypeConstants, 1).Select' selects only plain numbers within your selection.
    Sheets("bleugh").Range("A1").Copy '6
    Selection.PasteSpecial Operation:=xlSubtract
    Sheets("bleugh").Range("B1").Copy '-1
    Selection.PasteSpecial Operation:=xlMultiply
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
vba add and subtract code	phoenixmoto	Word VBA	6	11-06-2018 02:06 PM
Subtract value if cell contains X term	Jue92	Excel	4	11-01-2016 01:45 AM
Subtract value from one cell into another	otuatail	Excel	3	02-01-2016 03:21 AM
How to Subtract value in table, based on two different conditions	nitemath2	Excel	1	06-19-2015 01:28 AM
Formula to subtract one month from due date field in reminder field	ghumdinger	Outlook	1	10-01-2011 12:09 AM