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 cellsbut i dont know how to minus j5 dollars k5 cents and n5 dollars o5

all this calculated to p5 dollar amounts q5 cents amountexcel 16 mac air

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

Thank you Derby dad 03 that worked great much appreciated Brendan

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.

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 k20is that possible? thank you Brendan

f22 is dollar amounts g22 is cents

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)*1The 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.

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

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.

re: Thank you Derby dad 03 that worked great much appreciated BrendanPlease 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

centscalculation.I'll get back to you.

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

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

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

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 formulaany help would be great thanks Brendan

message edited by BRENDAN

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.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History