Question description:
This user has given permission to use the problem statement for this
blog.
Hi my name is Mark and I am using Excel 2011 for Mac. I have a spreadsheet that tracks personal financial accounts quarterly and I would like to compare a cell of the current quarter to a cell that contains results from the previous year and I need to create a formula to do this. For example if I have a spreadsheet for 2018 where cell A1 is the 1st qtr., cell B1 would be the 2nd qtr., C1 3rd qtr., D1 4th qtr. and cell E1 is for the results from 2017 and cell F1 would display the change for 2018. So if I only have the first quarter number entered in cell A1, cell F1 would display the results of A1-E1. But when the second quarter number is entered in cell B1, cell F1 needs to only display the results of B1-E1, and so forth for the remaining qtrs. as they get entered. Can you give me the formula to make this happen?
Thank you for your help,
Mark
Solved by T. C. in 36 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
25/01/2018 - 08:53
Hello! Welcome to Got It Pro-Excel.
Excelchat Expert
25/01/2018 - 08:56
We're so glad to support you of your Excel formula today.
Excelchat Expert
25/01/2018 - 08:57
Hi there
Excelchat Expert
25/01/2018 - 08:58
Can you share with us some part of your data for us to figure out as sample to create the formula for you?
Excelchat Expert
25/01/2018 - 09:00
Thanks for creating our sample at the Document preview
Excelchat Expert
25/01/2018 - 09:03
The formula for the remaining change at column F is now created. Please check if we got the expected output.
Excelchat Expert
25/01/2018 - 09:03
So, the formula looks like this,
Excelchat Expert
25/01/2018 - 09:03
=E2-sum(A2,B2,C2,D2)
Excelchat Expert
25/01/2018 - 09:05
wherein, we are getting the sum of the entered data from your column A, B, C and D, then to be subtracted by your expected total from column E (2017), to check the remaining value
Excelchat Expert
25/01/2018 - 09:06
You may change values at those cell, to see the result of your "change for 2018"
User
25/01/2018 - 09:06
F should read right now + 1000 for the 1st qtr.
Excelchat Expert
25/01/2018 - 09:08
The formula had been revised, please check once again
User
25/01/2018 - 09:09
Now with the second qtr added F should read 2000.
Excelchat Expert
25/01/2018 - 09:12
Your total for 1st and 2nd qtr will be 43,000 which is greater than your col E now having 20,000.
Excelchat Expert
25/01/2018 - 09:12
So, the result is over remaining of 23,000
Excelchat Expert
25/01/2018 - 09:13
May I know, how does F become 2,000 for us to revise the formula?
User
25/01/2018 - 09:15
This is what I am looking for; If 2017 had 20,000 and the first qtr. of 2018 had 21,000 the gain on that account is 1,000. Then when the 2nd qtr. figure of 22,00 is added F should show a gain of 2,000.
Excelchat Expert
25/01/2018 - 09:16
Alright, please give me few minutes to rectify the formula for you
Excelchat Expert
25/01/2018 - 09:21
The formula at F2 had been revised, please check
Excelchat Expert
25/01/2018 - 09:23
Any more concern regarding the formula?
User
25/01/2018 - 09:24
Looks great and to change the negative number to show in parentheses it would just be to change the format of the cell?
Excelchat Expert
25/01/2018 - 09:24
Thank you
User
25/01/2018 - 09:25
Thanks!
Excelchat Expert
25/01/2018 - 09:25
the negative part of the cell comes automatically as it execute the formula
Excelchat Expert
25/01/2018 - 09:26
The parenthesis to, made the options of those conditions/criteria
Excelchat Expert
25/01/2018 - 09:26
Well, is there anything else you want to know regarding this issue?
User
25/01/2018 - 09:26
I am good, thank you!
Excelchat Expert
25/01/2018 - 09:27
Thank you too for coming at Got It Pro-Excel!
Excelchat Expert
25/01/2018 - 09:27
Please click "End Session" when everything is fine with us and kindly give your kind feedback for our service.
Have a good day ahead!
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.
