Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

need help how to add a percentage to a column and subtract
Solved by B. U. in 43 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 05/09/2018 - 08:31
Welcome to ExcelChat!
User 05/09/2018 - 08:32
hi how do i put in column E-A minus 20% plus 2.5%
Excelchat Expert 05/09/2018 - 08:33
In which column do you want to have your result?
User 05/09/2018 - 08:34
E
User 05/09/2018 - 08:34
i meant F
Excelchat Expert 05/09/2018 - 08:34
Okay, so in column F you want to have Column E minus Column A minus 20% plus 2.5%, right?
User 05/09/2018 - 08:35
I want to have Column A minus 20% plus 2.5%, to be in comlun F
Excelchat Expert 05/09/2018 - 08:35
Okay, I got your task
Excelchat Expert 05/09/2018 - 08:37
Your result is ready
Excelchat Expert 05/09/2018 - 08:37
I made the formula in the following way:
Excelchat Expert 05/09/2018 - 08:37
=(A2*(100-20)/100)*(100+2.5)/100
User 05/09/2018 - 08:37
its not right
User 05/09/2018 - 08:38
its minus.20% plus 2.5%
Excelchat Expert 05/09/2018 - 08:38
If you want to change the %, you may simply change 20 and 2.5 to the required %
Excelchat Expert 05/09/2018 - 08:38
Yes, that is working like this now
Excelchat Expert 05/09/2018 - 08:38
Can you show the expected result?
Excelchat Expert 05/09/2018 - 08:38
I will show you a step by step calculation
Excelchat Expert 05/09/2018 - 08:39
Please have a look at H1:J2
Excelchat Expert 05/09/2018 - 08:41
If you want to make the calculations in another order, please, explain it, I will quickly update the solution
Excelchat Expert 05/09/2018 - 08:42
Just show me the right way
Excelchat Expert 05/09/2018 - 08:44
I also created another calculations in column G (in this way we first calculate the total difference in %, and then apply it to A2). Please, pick the right mechanics for your case.
User 05/09/2018 - 08:44
j should be 102
User 05/09/2018 - 08:44
tat is coorect anser
Excelchat Expert 05/09/2018 - 08:45
So the calculations should be based on column E?
Excelchat Expert 05/09/2018 - 08:45
Because you mentioned column A
User 05/09/2018 - 08:45
it should be based on A
Excelchat Expert 05/09/2018 - 08:45
And we can't get 102 from 51 by subtracting 20% from it and adding 2.5%
User 05/09/2018 - 08:46
sorry i meant multiplying!!
Excelchat Expert 05/09/2018 - 08:46
Oh.. that changes a lot
User 05/09/2018 - 08:46
i seitched it now its good
Excelchat Expert 05/09/2018 - 08:46
Glad that we figured it out
Excelchat Expert 05/09/2018 - 08:47
So everything is okay?
User 05/09/2018 - 08:47
can you show me how you made that formula
Excelchat Expert 05/09/2018 - 08:47
Here is your final formula:
Excelchat Expert 05/09/2018 - 08:47
=A2*(100-20)/100*2.5
Excelchat Expert 05/09/2018 - 08:48
So we just subtract 20% (it is the same as multiplying it by (100 - %) / 100
Excelchat Expert 05/09/2018 - 08:48
and then multiply it by 2.5
User 05/09/2018 - 08:49
what does this / 100 mean
Excelchat Expert 05/09/2018 - 08:49
It means divide by 100
Excelchat Expert 05/09/2018 - 08:49
If you want to subtract 30%from a number, for example
Excelchat Expert 05/09/2018 - 08:49
Then you can multiply it by 100 - 30, and divide by 100
User 05/09/2018 - 08:50
\why do you have to do that why cant you just do *2.5
Excelchat Expert 05/09/2018 - 08:50
I can replace it with 0.8 if you want
Excelchat Expert 05/09/2018 - 08:50
I just made it in a clear way so you could easily change these 20% to any other number
User 05/09/2018 - 08:50
yes it might be easier for mew
Excelchat Expert 05/09/2018 - 08:51
=A2*0.8*2.5
Excelchat Expert 05/09/2018 - 08:51
Is it better?
Excelchat Expert 05/09/2018 - 08:51
But now you now how we get this 0.8
User 05/09/2018 - 08:51
is that times 20% or minus?
Excelchat Expert 05/09/2018 - 08:52
Minus 20% and then multiply by 2.5
User 05/09/2018 - 08:52
=A2*0.8*2.5 you put in 2 multiply signs?
Excelchat Expert 05/09/2018 - 08:52
Yes.. this is the way how we subtract percent
Excelchat Expert 05/09/2018 - 08:53
If you want to remove 20% from a number, you can multiply it by (100-%)/100
Excelchat Expert 05/09/2018 - 08:53
I can remake the formula in another way, maybe it will be easier
User 05/09/2018 - 08:53
i am trying it myusdelf now to see if i can do it!@
Excelchat Expert 05/09/2018 - 08:53
I fully support you in it
Excelchat Expert 05/09/2018 - 08:54
That is why I try to make many solutions for you, as well as explain why I made it like this
User 05/09/2018 - 08:54
oh wow it qworked!!!
User 05/09/2018 - 08:54
let me try again
Excelchat Expert 05/09/2018 - 08:54
Yes, you did it right
User 05/09/2018 - 08:55
wow amazing but how can i get column a to get this formula transferred using this formula?
Excelchat Expert 05/09/2018 - 08:55
=(A2-A2*20%)*2.5
Excelchat Expert 05/09/2018 - 08:55
Another way of the same formula
Excelchat Expert 05/09/2018 - 08:56
You want to have this result in column A?
Excelchat Expert 05/09/2018 - 08:56
But our calculations are based on column A
User 05/09/2018 - 08:56
yes
Excelchat Expert 05/09/2018 - 08:56
So you can't have both value and calculations in one cell
User 05/09/2018 - 08:56
right how do i change i column itself
Excelchat Expert 05/09/2018 - 08:56
You can copy the result from column F and paste it as values to column A
User 05/09/2018 - 08:57
please show me
Excelchat Expert 05/09/2018 - 08:57
Simply copy F2:F14
Excelchat Expert 05/09/2018 - 08:57
Then select A2:A14
Excelchat Expert 05/09/2018 - 08:57
Right click - paste special -> values only
User 05/09/2018 - 08:58
it did not work
Excelchat Expert 05/09/2018 - 08:58
Select F2:F14
Excelchat Expert 05/09/2018 - 08:58
Copy
Excelchat Expert 05/09/2018 - 08:58
Select A2
Excelchat Expert 05/09/2018 - 08:59
Right click - paste special -> values only
Excelchat Expert 05/09/2018 - 08:59
or
Excelchat Expert 05/09/2018 - 08:59
ctrl + shift + v
User 05/09/2018 - 09:00
did you see i pasted down wrong
User 05/09/2018 - 09:00
how do i paste it down only the value
User 05/09/2018 - 09:00
or formula
Excelchat Expert 05/09/2018 - 09:01
Did you try using right click and selecting paste special?
User 05/09/2018 - 09:01
yes now how to i copy to rest of A column
Excelchat Expert 05/09/2018 - 09:01
Look
Excelchat Expert 05/09/2018 - 09:02
First you copy F2:F14
Excelchat Expert 05/09/2018 - 09:02
Do you know how to do that?
User 05/09/2018 - 09:02
how
User 05/09/2018 - 09:02
please show me
Excelchat Expert 05/09/2018 - 09:02
Can you select the cells from F2 to F14>
Excelchat Expert 05/09/2018 - 09:02
Select F2
Excelchat Expert 05/09/2018 - 09:02
Hold shift
Excelchat Expert 05/09/2018 - 09:02
Select F14
Excelchat Expert 05/09/2018 - 09:03
Release shift
Excelchat Expert 05/09/2018 - 09:03
Now hold the shift button down
Excelchat Expert 05/09/2018 - 09:03
and press F14
User 05/09/2018 - 09:04
whAT AM I DOING WRONG
Excelchat Expert 05/09/2018 - 09:04
Not sure.. there is another way
User 05/09/2018 - 09:04
ITS NOT WORKING
Excelchat Expert 05/09/2018 - 09:04
Hold the left button in F2
Excelchat Expert 05/09/2018 - 09:05
And drag the mouse own to F14
User 05/09/2018 - 09:05
HOW DO I COPY A VALUE DOWN THE ENTIRE COLUMN
Excelchat Expert 05/09/2018 - 09:05
Release the left button
Excelchat Expert 05/09/2018 - 09:05
Don't worry, I got your question.. and I am providing you with instructions
User 05/09/2018 - 09:05
I DID THAT
Excelchat Expert 05/09/2018 - 09:05
So you can't select multiple cells?
User 05/09/2018 - 09:06
I SELECTED THEM BUT NOW WHAT
Excelchat Expert 05/09/2018 - 09:06
Oh, great
Excelchat Expert 05/09/2018 - 09:06
I just see it as one cell seelction
Excelchat Expert 05/09/2018 - 09:06
Now press ctrl + c
Excelchat Expert 05/09/2018 - 09:06
Which means copy
User 05/09/2018 - 09:06
k
User 05/09/2018 - 09:06
done
Excelchat Expert 05/09/2018 - 09:06
now activate A2
Excelchat Expert 05/09/2018 - 09:06
Paste Special -> paste values only
User 05/09/2018 - 09:07
k
Excelchat Expert 05/09/2018 - 09:07
It looks like you copied only one cell from column F
Excelchat Expert 05/09/2018 - 09:08
Let's try in the third way to copy cells from column F
Excelchat Expert 05/09/2018 - 09:08
Please, follow me
Excelchat Expert 05/09/2018 - 09:08
Select F2
Excelchat Expert 05/09/2018 - 09:08
And press ctrl + shift + down
User 05/09/2018 - 09:09
k
Excelchat Expert 05/09/2018 - 09:09
Now press ctrl + copy, then A2 -> paste special -> paste values only
User 05/09/2018 - 09:09
yes it worked!!!!
Excelchat Expert 05/09/2018 - 09:10
Great to hear that
Excelchat Expert 05/09/2018 - 09:10
Besides that you learn how to make a multiple selection
Excelchat Expert 05/09/2018 - 09:10
But please, ask your friends to show you how to do it with a mouse
Excelchat Expert 05/09/2018 - 09:10
It is quite simple, if I were near, you would learn it in 5 seconds
Excelchat Expert 05/09/2018 - 09:10
It will make your life much easier while working with Excel
Excelchat Expert 05/09/2018 - 09:11
Because multiple selection of the cells is a must
User 05/09/2018 - 09:11
i see now it worked with the mouse!
Excelchat Expert 05/09/2018 - 09:11
Oh, just perfect
Excelchat Expert 05/09/2018 - 09:11
I am glad you figured it out
Excelchat Expert 05/09/2018 - 09:11
By the way, what did you do wrong the first times?
User 05/09/2018 - 09:11
how do i put the value in a15 and a16 so i can put in a num,ber and it goes to te number i need
Excelchat Expert 05/09/2018 - 09:13
Sorry.. but we are allowed to solve only one problem per session. I have explained you how to make the selection and paste values only because I consider it as a must for using the Excel. But I can't solve multiple problems per session, because I will be punished for that later. Hope for your understanding.
User 05/09/2018 - 09:13
when ever i drag it dopwn it brings dowen the number but not the value
User 05/09/2018 - 09:14
yes thank you you were great can we extend the session and i will pay
Excelchat Expert 05/09/2018 - 09:14
To solve the new problem, please start a new session
Excelchat Expert 05/09/2018 - 09:14
Sure, just end this session, leave me a feedback and start a new one
User 05/09/2018 - 09:14
i t did not let me start a new one before

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc