Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Calculating Question with Excel

Old 05-16-04, 04:11 PM
  #1  
DVD Talk Special Edition
Thread Starter
 
Join Date: Apr 2002
Location: FL.
Posts: 1,476
Calculating Question with Excel

I'm working on a spreadsheet in Excel and would like to have certain cells calculate values based on values I have entered in other cells. This in itself is fairly easy. However, I am running into a problem with the way the value is displayed in one cell.

Here's what I'm running into:
  • In cell C12 I enter the value $2,079.26
  • In cell D12 I enter the value $456.56
  • In cell E12 I have a calc to subtract D12 from C12 which gives me $1,622.70
  • In cell F12 I enter a percentage (25%)
  • In cell G12 I have calculating 25% of the value in E12 (E12*F12) which equals 405.675 but is displayed as $405.68
  • In cell I12 I want to calc the value of E12-G12. This value equals 1217.025.
I want this to display as $1217.02, but Excel is rounding the value to $1,217.03. This is causing my numbers to be slightly off.

Is there a way to format the cell I12 so it displays the value as $1,217.02 rather than $1,217.03?
Spy021 is offline  
Old 05-16-04, 08:53 PM
  #2  
DVD Talk Limited Edition
 
Jack Straw's Avatar
 
Join Date: May 1999
Location: So. Calif.
Posts: 5,024
In cell I12, subtract .001 as follows:

=SUM(+E12-G12)-0.001
Jack Straw is offline  
Old 05-16-04, 10:02 PM
  #3  
DVD Talk Special Edition
Thread Starter
 
Join Date: Apr 2002
Location: FL.
Posts: 1,476
Originally posted by Jack Straw
In cell I12, subtract .001 as follows:

=SUM(+E12-G12)-0.001
That would seem to take care of my problem in that particular scenario. However, it would still remain a problem if my term digit was say 6-9?
Spy021 is offline  
Old 05-16-04, 10:20 PM
  #4  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,704
I didn't go through the individual calculations, but what is the problem? Is it giving you too large a number due to bad rounding or are you trying to use the rule of .005 only rounding up odd numbers in the cents?

You can always use a truncate function to cut off the number after two decimal places if you just want to drop anything past the cents.
X is offline  
Old 05-16-04, 10:46 PM
  #5  
td
New Member
 
Join Date: Mar 2000
Posts: 14
In I12, do =rounddown(e12-g12,2)

-td
td is offline  
Old 05-16-04, 11:13 PM
  #6  
DVD Talk Special Edition
Thread Starter
 
Join Date: Apr 2002
Location: FL.
Posts: 1,476
Originally posted by X
I didn't go through the individual calculations, but what is the problem? Is it giving you too large a number due to bad rounding or are you trying to use the rule of .005 only rounding up odd numbers in the cents?

You can always use a truncate function to cut off the number after two decimal places if you just want to drop anything past the cents.
I believe the problem is due to the fact that when I take a percentage of value in E12 ($1,622.70) it factors out to 3 decimal places rather than 2.

Originally posted by td
In I12, do =rounddown(e12-g12,2)

-td

Thanks TD!
Spy021 is offline  
Old 05-17-04, 01:12 PM
  #7  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: High Definition
Posts: 24,970
EDIT: Spy021, read the entire thread first before DOing anything. -namja


Originally posted by td
In I12, do =rounddown(e12-g12,2)

-td
I think what Spy021 wants to do is to take E12 and subtract the rounded number from G12, then put that result in I12. IF that is the case, then what you are suggesting is not much better than Jack Straw's suggestion, in that it will work only in THIS INSTANCE. With that formula in I12, it will mess up half the time. For example, if the value in E12 = 1,622.69, then:

E12 = 1,622.69
G12 = 405.6725 ---> rounded to 405.67
I12 = 1,217.0175 --> rounded down to 1,217.01

For any value in G12 that is rounded down, the rounddown function in I12 "messes up" the result. 1,622.69 - 405.67 does not equal 1,217.01.


Anyway, Spy021, this is unconventional, but try this for G12:
=FIXED(E12*F12,2,FALSE)

That will return you a number in text format with the number rounded 2 digits and the rest chopped off. Even though the number is output as text format, you can still do math on it so you should get a favorable answer in I12. You'll probably want to click on "right align" for G12 to make that numbers aligned the same way as the other numbers.

Enjoy.
namja is offline  
Old 05-17-04, 01:21 PM
  #8  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,704
That's essentially what I was saying by using a truncate function.

Truncate just works on integers so you could say =TRUNC((E12-G12)*100)) / 100
X is offline  
Old 05-17-04, 01:28 PM
  #9  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: High Definition
Posts: 24,970
Well, kinda. The TRUNC function does not round before chopping off everything after a certain number of digits. It will truncate 128.9875 to 128.98. It's only 1 cent so maybe it's not a big deal.

Also, instead of doing:
=TRUNC((E12-G12)*100)) / 100
You can simply do:
=TRUNC(E12-G12,2) which will truncate to 2 decimals.
namja is offline  
Old 05-17-04, 01:30 PM
  #10  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,704
Oh, ok. I was thinking truncation was the object here but I could have misunderstood.

Then it's even easier. Just use the Round function.

=ROUND(E12-G12,2)
X is offline  
Old 05-17-04, 01:33 PM
  #11  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: High Definition
Posts: 24,970
Originally posted by X
Oh, ok. I was thinking truncation was the object here but I could have misunderstood.

Then it's even easier. Just use the Round function.

=ROUND(E12-G12,2)
Excellent. Good stuff.
namja is offline  
Old 05-17-04, 04:57 PM
  #12  
DVD Talk Special Edition
Thread Starter
 
Join Date: Apr 2002
Location: FL.
Posts: 1,476
Got it (too)!

Thanks guys... based off a few ideas I read on the boards earlier, I sat down and looked into the Round function while on a break this morning. After playing around for about 10 minutes, I came up with exactly the same thing that X layed out. Guess I just needed a little push in the right direction. Thanks again guys for the help!
Spy021 is offline  
Old 05-17-04, 06:01 PM
  #13  
Uber Member
 
Join Date: Mar 1999
Location: Overlooking Pearl Harbor
Posts: 16,232
This is bizarre. I just had to do this exact same thing not 1 hour ago.
Blade is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.