+ Reply to Thread
Results 1 to 4 of 4

excel incorrect handles division when using INDIRECT

  1. #1
    Registered User
    Join Date
    01-12-2017
    Location
    London, Ontario
    MS-Off Ver
    2016
    Posts
    2

    excel incorrect handles division when using INDIRECT

    I am having a very wierd problem. if i put everything into a single cell i get the wrong answer, but if i take the formula and split half of it into a second formula i get the right answer.

    Input:

    M2: 1:10 PM
    N2: 8:10 PM
    AD1: 2:00 PM
    AD2: =(INDIRECT("R1C"&COLUMN(),FALSE))-(INDIRECT("M"&ROW())) --> output is 0.034722222
    if i multiply by 24 i get a negative value:
    AD2: =(INDIRECT("R1C"&COLUMN(),FALSE))-(INDIRECT("M"&ROW()))*24 --> output is -12.58333333 (bad)
    if i then divide by 24 again i get the original input
    AD2: =(INDIRECT("R1C"&COLUMN(),FALSE))-(INDIRECT("M"&ROW()))*24/24 --> output is 0.034722222

    now that we have established wierd behavior with multiplication we will then do the multiplication in a different field

    AD2: =(INDIRECT("R1C"&COLUMN(),FALSE))-(INDIRECT("M"&ROW())) --> output is 0.034722222
    AE2: =AD2*24 --> output is 0.83 (good)

    i'm actually trying to do the following:
    AD2: (INDIRECT("N"&ROW())-INDIRECT("R1C"&COLUMN(),FALSE))/(INDIRECT("N"&ROW())-INDIRECT("M"&ROW()))

    the reason is i have 1 column per hour and i'm trying to allocate workload as a % of the original time range (M,N) and computing a factor to multiply by another number.

    i've seen oddities before but this one is particularly special. i was hoping if i multiplied by a constant it would work because then i could move the "divide" part into another cell but it looks like if i subtract the times and multiply or divide in the same cell i get a bad answer.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: excel incorrect handles division when using INDIRECT

    This looks like one of my most common algebra errors -- location and number of parentheses. Replacing INDIRECT() functions with simple variables, I get the following for your examples:

    (x)-(y)=0.0347222
    (x)-(y)*24=-12.583 Note how only Y is multiplied by 24.
    (x)-(y)*24/24 =0.0347222 Note again how only Y is multiplied by 24/24.

    When you put the multiplication into a separate cell, then it effectively becomes
    ((x)-(y))*24 Note how the added parentheses cause the subtraction to occur before the multiplication so that the *24 applies to the entire operation, not just y.

    This would be a case where using the Evaluate formula tool (https://support.office.com/en-us/art...6-a70aa409b8a7 ) would have likely readily shown you that Excel was performing the multiplication before the subtraction.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    01-12-2017
    Location
    London, Ontario
    MS-Off Ver
    2016
    Posts
    2

    Re: excel incorrect handles division when using INDIRECT

    even after checking 3 times i overlooked it, i had an extra set of brackets, excel must have tried to "help" at somepoint and dropped them in.

    =(INDIRECT("R1C"&COLUMN(),FALSE))-(INDIRECT("M"&ROW()))*24
    Last edited by lachlann562; 01-13-2017 at 10:04 AM.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: excel incorrect handles division when using INDIRECT

    I would be curious what brackets you added or any other edits you tried. Keeping track of parentheses is one of my least favorite parts of algebra and programming. If the forum software will render it correctly, I use a strategy like this to try to keep track of parentheses:
    Please Login or Register  to view this content.
    It still appears to me that you have not correctly matched the parentheses. The leftmost open parenthesis (that you claim matches the rightmost close parenthesis) actually appears to match the close parenthesis immediately before the subtraction sign. The rightmost close parenthesis appears to match the opening parenthesis immediately after the subtraction sign before the second INDIRECT() function. Again, I don't know what variations you tried, but it still appears to me that the solution to your question is to get the parentheses correctly placed in the formula. Once the parentheses are correctly placed, then I would expect the formula to calculate correctly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Simple division for multifactor productivity ratio is incorrect
    By FoxfaceMBA in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-10-2015, 09:30 PM
  2. Macro for division with absolute cell references to replace Indirect formula?
    By naira in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-14-2013, 01:37 AM
  3. [SOLVED] How to remove or hiding sizing handles in excel shapes
    By hmaleki in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-22-2013, 07:01 AM
  4. Charting question - How Excel handles dates
    By johnekeefe in forum Excel General
    Replies: 7
    Last Post: 06-10-2012, 02:52 PM
  5. How excel handles names in multiple sheets
    By dmartindale in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2007, 01:58 PM
  6. Incorrect division by zero when using DLL function
    By manik in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2007, 05:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1