Solved how to do this calculation

June 20, 2017 at 21:03:37
Specs: Macintosh
i am trying to do a series of calculation i need to add dollar amounts in d5 cents in e5 then h5 dollar i5 cents then minus j5 dollar k5 cents add l5 dollar m5 cents minus n5 dollar o5 cents all formulated to total in p5 dollars q5 cents

i have the adding side of it working with this formula

=sum(d5,h5,l5)+int(sum(e5,i5,m5)/100 put in th dollar amount cells and
=right(sum(e5,i5,m5),2)*1 put in the cent cells

but i dont know how to minus j5 dollars k5 cents and n5 dollars o5
all this calculated to p5 dollar amounts q5 cents amount

excel 16 mac air


See More: how to do this calculation

Report •

#1
June 21, 2017 at 08:25:28
✔ Best Answer
How about these:

=SUM(D5,H5,L5,-J5,-N5)+INT(SUM(E5,I5,M5,-K5,-O5)/100)

=RIGHT(SUM(E5,I5,M5,-K5,-O5),2)*1

Basically, I'm doing the "subtraction" within your SUM functions by turning your subtraction values into negative numbers. After that, the rest of your original formulas take over.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
June 21, 2017 at 08:39:25
Thank you Derby dad 03 that worked great much appreciated Brendan

Report •

#3
June 21, 2017 at 08:41:05
Glad I could help. Did you see the edited version, the one without the additional SUM functions?

Just a little shorter.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 21, 2017 at 17:16:58
new problem
this whole extra column thing for this sheet really confuses me when it comes to combining those 2 columns dollar and cent amounts together
i need to get 5% of sales from box f22 and g22 cents to appear in box k18
and 21/2 % of the same sales numbers in the same boxes to appear in box k20

is that possible? thank you Brendan


Report •

#5
June 21, 2017 at 17:18:05
f22 is dollar amounts g22 is cents

Report •

#6
June 21, 2017 at 19:04:03
Why are the columns separated? Why not combine them, do your calculations directly on the overall value and then separate the result afterwards?

Create some "Helper Columns" anywhere you want on the sheet or even on another sheet.

First, combine the Dollars and Cents:

=F22+G22/100

Do your math on those values, get a result.

Then separate the result:

Dollars: =LEFT(result,LEN(result)-3)*1
Cents: =RIGHT(result,2)*1

The Helper columns could even be hidden so they don't get in the way visually.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
June 21, 2017 at 22:30:30
Thank you for the help but thats way over my head,I have no idea how to go about this
I'll look up helper columns online and try to figure out what you are talking about.
I am new to all this but thank you for answering me thank you

Report •

#8
June 22, 2017 at 04:04:44
A Helper column is nothing more than a name for a technique where you use to do an intermediate calculation that helps you get to the final result. Sometimes it's hard to find a single formula that gets you the answer you need, so you break it down into smaller steps.

In this case you are struggling because the dollars and cents are in separate columns. I can certainly understand that that is a cumbersome process. That is why I am suggesting that you combine the dollars and cents into a single value, do your calculations on that value, and then separate the result out into dollars and cents afterwards. One Helper column would be a column where you combine the dollars and cents and another would be the one where you do the calculation on the combined value. The columns where you want the final values would contain the LEFT and RIGHT functions to split that result back out into dollars and cents.

I can't post an example right now, but I should be able to in a few hours.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
June 22, 2017 at 07:24:21
re: Thank you Derby dad 03 that worked great much appreciated Brendan

Please hold off on thanking me. After further testing, I'm not sure that my original formulas actually work. There appears to be an issue when the subtraction portion returns a negative number, especially in the cents calculation.

I'll get back to you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
June 23, 2017 at 06:39:29
OK, so here is a solution that doesn't require any Helper columns. It does all of the calculations by first combining the Dollars and Cents into a single value, e.g. 231.45, then performing the math you want to do, then separating out the Dollars and Cents from the final result.

By combining the Dollars and Cents into a single value, we eliminate the error caused when the subtraction values are large. These combination calculations could be done in a separate helper column, but if you don't mind a long formula, they can all be done at once.

Going back to your original question, put this in P5 to get the Dollar figure:

=TRUNC(SUM(D5+E5/100,H5+I5/100,L5+M5/100,-(J5+K5/100),-(N5+O5/100)))

Explanation:

1 - Each of the D5+E5/100 style arguments combine the respective Dollars and Cents into single values
2 - The SUM function does your Addition and Subtraction using the single values
3 - The TRUNC function strips off the decimal (Cents) portion of the result. I used TRUNC instead of INT because INT actually rounds the value down to the next lowest whole number. If your subtraction values are larger than your addition values, INT will return the wrong value.

Put this in Q5 for your Cents figure:

=(SUM(D5+E5/100,H5+I5/100,L5+M5/100,-(J5+K5/100),-(N5+O5/100))-P5)*100

Explanation:

1 - Each of the D5+E5/100 style arguments combine the respective Dollars and Cents into single values
2 - The SUM functions do your Addition and Subtraction using the single values
3 - Subtracting P5 (the Dollar portion) leaves the decimal portion (Cents)
4 - Multiplying that result by 100 turns the Cents into a whole number.

For your other problem (percentages of F22:G22), we can basically use the same "combine" technique.

=(F22+G22/100)*5%

Let me know if you have any problems with these suggestions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#11
June 24, 2017 at 08:31:02
Thank you Derbydad03 that works great thank you for your input and efforts to solve this for me,I really appreciate it ,it's a real learning curve for me, and your explanations of what's going on in the background helps me learn more about excel each time you answer me

Brendan


Report •

#12
June 24, 2017 at 19:04:45
I have this macro to clear data from cells so the sheet can be reused these are all unprotected with no formulas in them Worksheets("good one protected").Unprotect Password:=

Range("C5:O11").Select
Selection.ClearContents
Range("F16:H20").Select
Selection.ClearContents
Range("K18:L20").Select
Selection.ClearContents
Range("N18:N22").Select
Selection.ClearContents
Range("P18:Q22").Select
Selection.ClearContents
Range("K22:L22").Select
Selection.ClearContents
Range("c1").Select
Selection.ClearContents
but i have 3 ranges k18 to k22 n18 to n22 p18 top22
that have formulas in them can you tell me how to delete the data but keep the formulas
using via in this formula

any help would be great thanks Brendan

message edited by BRENDAN


Report •

#13
June 25, 2017 at 05:28:55
Please post your macro question in a separate thread with a relevant subject line.

When posting in a Help forum, it is best to dedicate a single thread to a specific question. That makes it easier to follow the discussion and it helps keep the archive organized.

In addition, it is best to use a subject line that tells us something about the issue that you are trying to resolve. If everyone used subject lines such as "How to do this calculation" or "Need help with a Excel" or "I need a macro" we wouldn't be able to tell one question from another and the archives would be very hard to use.

Finally, please click on the following link and read the instructions on how to post Data and Macros in this forum so that they are easier for us to read. You should use the pre tags when you post your VBA code in the new thread.

For example, in the new thread that you are about to create, something like "How To Prevent Macro From Deleting Formulas" would be appropriate.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question