Formulas not calculating correctly

How do I get formulas to calculate correctly?

I have used the following simple formula -

=T20*Z20

where t20 = =IF(N20>100%,"0",K20-M20)
z20 = 917,000
n20 = 99.04%
k20=394
m20=390

When I do the math, it should total 3,668,000, yet my total in excel is 
3,457,090.  Why is excel calculating this incorrectly?


0
MWickline (2)
6/9/2005 2:33:04 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
289 Views

Similar Articles

[PageSpeed] 1

Hi
I think its rounding problem if type figures given into excel it calculates 
3668000 I presume figures in row 20 are sums of ranges
Tina

"MWickline" wrote:

> How do I get formulas to calculate correctly?
> 
> I have used the following simple formula -
> 
> =T20*Z20
> 
> where t20 = =IF(N20>100%,"0",K20-M20)
> z20 = 917,000
> n20 = 99.04%
> k20=394
> m20=390
> 
> When I do the math, it should total 3,668,000, yet my total in excel is 
> 3,457,090.  Why is excel calculating this incorrectly?
> 
> 
0
Tina (84)
6/9/2005 2:53:03 PM
I just checked it on a new spreadsheet and got 3,668,000.   You may want to 
review some of the results to see if you are getting a round off error.   I'd 
check K20 and M20 with more decimal places to see if you really have what you 
think you have.    I suspect that you have K20-M20=3.77 instead of 4.

"MWickline" wrote:

> How do I get formulas to calculate correctly?
> 
> I have used the following simple formula -
> 
> =T20*Z20
> 
> where t20 = =IF(N20>100%,"0",K20-M20)
> z20 = 917,000
> n20 = 99.04%
> k20=394
> m20=390
> 
> When I do the math, it should total 3,668,000, yet my total in excel is 
> 3,457,090.  Why is excel calculating this incorrectly?
> 
> 
0
BarbR (262)
6/9/2005 2:55:03 PM
That was it.  K20 is a sum of a few other cells, where one is a decimal.  
Thank you so very much.
-- 
MWickline


"Barb R." wrote:

> I just checked it on a new spreadsheet and got 3,668,000.   You may want to 
> review some of the results to see if you are getting a round off error.   I'd 
> check K20 and M20 with more decimal places to see if you really have what you 
> think you have.    I suspect that you have K20-M20=3.77 instead of 4.
> 
> "MWickline" wrote:
> 
> > How do I get formulas to calculate correctly?
> > 
> > I have used the following simple formula -
> > 
> > =T20*Z20
> > 
> > where t20 = =IF(N20>100%,"0",K20-M20)
> > z20 = 917,000
> > n20 = 99.04%
> > k20=394
> > m20=390
> > 
> > When I do the math, it should total 3,668,000, yet my total in excel is 
> > 3,457,090.  Why is excel calculating this incorrectly?
> > 
> > 
0
MWickline (2)
6/9/2005 3:10:05 PM
Reply:

Similar Artilces:

Bolding/etc based on formula results...
Hi folks, I want to insert a formula into a cell, whereby if the results were outside a certain range, the results would be bolded, and the cell possibly infilled with a different background colour. If the results of the formula fell within the certain range, then the results would appear as normal type, and the background colour would be the same as the rest of the spreadsheet. Any pointers on how I can do this? Cheers, Dave A formula cannot change a cell's format But Format | Conditional Formatting can do just what you want. Have a look at it, experiment, then return with questions...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

Assigning Calculated value to main report from subreport
I have a report with a subreport in it. I am trying to sum up the values of a txt-box [Extended Price] in the subreport and put the value in a txt-box, "TotalAmountIS" on the main report. This is what I did: 1.) I created a txtbox in the footer of the subreport and called it TotalAmount 2.) I assigned "=sum([Extended Price])" to the control source of TotalAmount 3.) I created a txtbox on the main report 4.) I then assigned "=Nz([subreportname]![TotalAmount],0)" to the control source of TotalAmountIS This was the same way I did it on a form, with ...

Filling bound controls in a form with calculated results
I have a form that a user may partially fill with text and numbers that could then be used to calculate numerical results that could fill in other blank fields in the same form. My idea would be to have a un-bound button in the upper title bar of the form that the user could click after filling in some of the initial fields, and this action would calculate numerical results via VBA functions or procedures, the results then being used to fill in multiple field values I know how to write the VB functions/procedures to calculate the results, but I'm not sure where to use the functions...

Formula for adding up columns, i.e., B1:B10 + D1:D10 + F1:F10 + H1:H10 + J1:J10
I've never figured this one out. Went and googled yet again but I'm obviously not googling for the right thing. Same as with the help file. What is the type of formula we use for adding up columns or rows, esp. if they're broken up by rows/columns in between? Thanks! =SUM(B1:B10,D1:D10,F1:F10,H1:H10) -- Regards, Peo Sjoblom "StargateFanFromWork" <noSpam@NoJunkMail.com> wrote in message news:ORPkLEa1FHA.2792@tk2msftngp13.phx.gbl... > I've never figured this one out. Went and googled yet again but I'm > obviously not googling for the right t...

How to get rid of "A formula in this worksheet contains one or more invalid references"
Frequently I'll delete some cells in an Excel 2007 worksheet and from then on get the message "A formula in this worksheet contains one or more invalid references." whenever I try to do anything in the worksheet. There are no invalid references, as I have verified. Possibly there were some temporarily while Excel was in the process of deleting the cells. If I save the file, exit Excel, then reopen the workbook, Excel changes its mind and decides there aren't any invalid references after all (at least the message goes away). Is there any way to get Excel to figure t...

how do you delete cells when they are connected with a formula
All I know how to do is enter info into a cell. This is a list of numbered employees. Usually, I would just seleted the cell and delete, but I get ###error. The formula is =+sum (A73+1) Please help!!! I believe you are seeing ### because the cell is not large enough to display the #REF! error. When you delete the cell (A73), either by right clicking and seleting delete or by using the Edit function, the formula no longer knows what cell to reference, and all the other cells below the one with the error have all adjusted accordingly (in this case, up one). If you look below the cell w...

Can i use conditional formating on a cell when it contains a formula? #2
=mid(text(a1;"0000000000.00");1;1 -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi the reason for this is that thie formula returns a string and not a numeric value. Change the formula to: =--mid(text(a1;"0000000000.00");1;1) -- Regards Frank Kabel Frankfurt, Germany sit wrote: > =mid(text(a1;"0000000000.00");1;1) OR ... Change your Conditional formula to: =&quo...

help with distribution formula!
i need to make a distribution table. i know that i use the -normdist- function, i just can't figure out what to put in each argument. my data is 10 questions, and each question has 5 answers to choose from (multiple choice). Assuming that im making complete random guesses, i need to find the probability of getting 1 question correct, 2 questions, 3, 4, and so on until i get the prob. of getting all 10 correct. what would i plug into each argument? x - mean - standard Dev - cumulative - i'm not specifically asking for the answers, but more for help on figuring out which goes into...

Vlookup Formula #3
Hi, column J has dates (days of each month). Column K has numbers. the whole rang is J7:K38. E12 has a date and F12 has a number. In cell G12, I need to: =F12*K? (that has the corresponding date as in cell E12) ie. if E12 has date 14/10/07 then look for the same date in range J7:J39 and take the corresponding number in range K7:K39 and multiply it with the value in f12 and put the answer in cell G12. All in the same s Khalil] Hope to have an answer!! =VLOOKUP(E12,J7:K38,2,FALSE)*F12 if you indent to copy the formula down the column =VLOOKUP(E12,$E$7:$K$38,2,FALSE)*F12 best wishes -- Be...

Formulas are inactive
I have completed a five sheet program for monitoring Mutual Fund Data. All of the formulas acted normally until I completed a sixth sheet to summerize the data. The formulas on the sixth sheet link back to the other five sheets. Upon completetion of the sixth sheet none of the formulas in the preceding five sheets will work. Help Pete Check out Tools>Options>Calculation. Is is set to "manual" or "Automatic"? Gord Dibben Excel MVP On Sun, 14 Dec 2003 10:51:10 -0800, Pete S <psimler@twmi.rr.com> wrote: >I have completed a five sheet program for monitorin...

how do I calculate times and get as a result numbers
I got a Spreedsheet that I whant to use as a time sheet, this is so far what I got done wrong what I want to get is right a1= 10:00am time in a1= 10:00am b1= 2:00pm time out (lunch) b1= 2:00pm c1= 2:30pm time in (lunch) c1= 2:30pm d1= 8:00pm time out d1= 8:00pm e1= 8:30 total e1= 8.5 the answer has to be a number if the time is #:0<= #:14 = #.0 #:15<=#:29 = #.25 ...

VLOOKUP not working correctly!
hi I have been using VLOOKUP on a few reports recently and it has been working great, however, cannot seem to get it working on a particular field. I am linking from a master file and linking to current workbook. When you select the job number in the workbook it populates all the address fields etc fine from the master file, and also copies the name ofr the Project Manager in (Cell Ref: D25), however from here I have a table that details the mobile numbers and emails address of each on seperate tab of current workbook called "Internal Contacts" thus: =VLOOKUP(D25,'Internal Con...

[b]calculating Month Average For Exch Rates From Data Array
[B]Hello, All, need Yr help in formula creating. We have exchange rate calendar (USD to Ukranian Hryvna) by every banking day in the year. Data organized as following: col A - dates DD/MM/YYYY, col B - appropriate exchange rate, for instanse - 5,05. I need to have in col C average month exchange rate. So do i need to recognize that date belongs to the same month in the same year and then to include appropriate rate to array for calculating an average? that's my idea... Please comment thanx in advance -- StanUkr ------------------------------------------------------------------------...

Excel Formula #12
I am trying to have text and a formula reside in teh same box with the date in the format shown (23-May-04). Here is as far as I get... ="Todays date is "&TODAY() and the results I get is Todays date is 38131 Rick Rick, here you go: ="Today's date is "& TEXT(TODAY(),"dd-mmm-yy") -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "Rick" <ricky.sather@si-intl.com> wrote in message news:7c55e5aa.0405241127.3cebb0ab@posting.google.com... > I am trying to have text and a formul...

RE: Install the correction update from the Microsoft
--cdfjsysqlcdpu Content-Type: multipart/related; boundary="lgtoqnldawbpqmrot"; type="multipart/alternative" --lgtoqnldawbpqmrot Content-Type: multipart/alternative; boundary="oiaosghhnqjyoimw" --oiaosghhnqjyoimw Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "September 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilit...

Need formula to calculate time difference
I am certain there is a really simple way of doing this and I am just too stupid to figure it out... I just want to have Row 1 Column B show a start time I manually enter (say 9:15PM), then be able to enter 10 (ten minutes) in row 2 column A to show a value of 9:25PM in row 2 column B. Row 2 Column B has time value (9:20PM) Row 2 Column A has minutes allowed (20) In other words, add B2 and A3 and show them as a new time in B3. Any takers??? Hi, if you type just 10 on a2 the formula should be =b1+time(0;a2;0) if you type 00:00 is just b1+a2 hth regards from Brazil Marcelo "mar...

Adding TEXT in front of a DATE formula
Hello everybody, Is it possible to add text in front? I am not getting there with this setup. Can someone help? =LEFT&"Date Printed:"&TODAY() Thanks Ray, ="Date Printed: " & TODAY() Note this will show up as something like: Date Printed: 37903 Probably not what you wanted. But, you can use the TEXT function to format the date... ="Date Printed: " & TEXT(TODAY(), "MMMM DD, YYYY") Date Printed: October 9, 2003 ="Date Printed: " & TEXT(TODAY(), "MM/DD/YYYY") Date Printed: 10/09/2003 etc... Dan E "Ray...

Calculating for personal car mileage?
I want to create a formula that will calculate the amount of dollar due employee if he uses his personal car for business purposes. I cannot figure out how to avoid to overwrite the formula I created to perform that function. For example, my formula is "=A1*0.485", but the formula is over written every time I enter 10 (miles) in A1. How do I solve this problem? Please help!!! Elau wrote: > I want to create a formula that will calculate the amount of dollar due > employee if he uses his personal car for business purposes. I cannot figure > out how to avoid to overwrit...

Multiplication formulas....
Hello, A 2 part question here. Im completely new to excel so I apologize for my lack of terminology.... 1) Is it possible to multiply all the numbers in a column by a single fixed number? For example, the column is a list of prices in US currency but, I would like to convert them all to Canadian currency. Can I do this without having to manually do it to each cell? Also, can I have the answer come up in a different column? 2) Is there a way to multiply 1 set of numbers by numbers in a seperate column. To continue with the previous example, can the numbers in the *'prices'* column be...

Excel Formula depending on number in cell..
Hello everyone! I have a question that I thought some of you could answer: I have a number in column D and based on if it is $0-$100 I want i multiplied by 0.10 and then rounded up to .95. So, if it is 20.49 + 10% = $22.54 and then rounded up to $22.95 Now this is where it gets tricky.. If it is between 100.01 to 200.00 + 7% and then rounded up to a .95. So for example 129.44 + 7% = $9.08 = $138.50 then rounded up to .95 $138.95. I need to do this for a range of numbers like 200.00 to 300.00 an 300.00 to 500.00 etc, etc.. Could someone point me in the right direction? I appreciate a...

VBA code to find all formula cells with a variable and a constant
Using XL 2003 & 97 Not yet successful at trying to write VBA code to find all formula cells with a variable AND a constant. Also, I would like each identified cell to background-fill with yellow. Example: (Cells A2 & A3 are background-filled yellow) A1 = B1+C1 (Not selected - background no-fill) A2 = B2+10000+C2 (Selected - background - yellow) A3 = B1+C1+15000 (Selected - background - yellow) TIA Dennis ...

Excel 2007 formula, multiple reference range
Help. I am collating data from 6 facilities. I have one worksheet with 7 pages. The first is a summary of all data. The other 6 have individual data for each facility. On the summary page, I need a total number, pulled from all 6 facilities. On the facility detail pages, I have summed the occurrences in that facility. Now, I need a formula that will let me get a grand total. For example: pg 1 B5 needs to have the total from: pg2 K5:K100 if J5:J100 = pg1A5 plus pg3 K5:K100 if J5:J100 = pg1A5 plus pg4 K5:K100 if J5:J100 = pg1A5 plus pg5 SAA plus pg 6 SAA plus pg 7 SAA The reason w...

Count formulas
I am trying to do a set of 3 counts between 1-100. Under 80 Between 80-90 and 90+. I have 2 formulas that are working and can't seem to figure the 3rd, could anyone help? 1st =COUNTIF(G4:K22,">89.99") 2nd =COUNTIF (G4:K22,"<79.99") It is the middle one that I am not able to figure out. count all -those two or =sumproduct((g4:k22>=80)*(f4:k22<90)) -- Don Guillett SalesAid Software donaldb@281.com "Barb" <anonymous@discussions.microsoft.com> wrote in message news:045801c3d85f$f193bdb0$a401280a@phx.gbl... > I am trying to do a set of...

How to eliminate zero values on graphs when formula calculates 0?
I'm setting up a graph of data that is calculated with an "if" function over a series of days of input data including future days. When the input data cell is blank I would like the calculated value to not show a zero value on the graph. If the cell were blank, the plot would not show a zero value. The problem is that at the end of the line graph the line always falls off to zero. =IF(A1="",NA(),your_formula) -- David Biddulph "sjgolfer" <sjgolfer@discussions.microsoft.com> wrote in message news:29E4FF52-DC7D-4419-A5EF-63BAFF93E729@microsoft....