Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-24-2019, 11:08 AM
tuix tuix is offline Subtract from X formula? Windows 10 Subtract from X formula? Office 2019
Novice
Subtract from X formula?
 
Join Date: Jun 2019
Posts: 1
tuix is on a distinguished road
Default Subtract from X formula?

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.
Reply With Quote
  #2  
Old 06-24-2019, 03:15 PM
Alansidman's Avatar
Alansidman Alansidman is offline Subtract from X formula? Windows 10 Subtract from X formula? Office 2019
Advanced Beginner
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 47
Alansidman is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 06-24-2019, 03:21 PM
Alansidman's Avatar
Alansidman Alansidman is offline Subtract from X formula? Windows 10 Subtract from X formula? Office 2019
Advanced Beginner
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 47
Alansidman is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 06-25-2019, 12:03 AM
ArviLaanemets ArviLaanemets is offline Subtract from X formula? Windows 8 Subtract from X formula? Office 2016
Expert
 
Join Date: May 2017
Posts: 573
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

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!
Reply With Quote
  #5  
Old 06-27-2019, 06:03 PM
p45cal p45cal is offline Subtract from X formula? Windows 10 Subtract from X formula? Office 2016
Expert
 
Join Date: Apr 2014
Posts: 396
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

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
Reply With Quote
Reply

Tags
ecxel formula, subtract from

Thread Tools
Display Modes


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 from X formula? 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
Subtract from X formula? 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 09:47 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft