# excel incorrect handles division when using INDIRECT

1. ## 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
AD2: =(INDIRECT("R1C"&COLUMN(),FALSE))-(INDIRECT("M"&ROW())) --> output is 0.034722222
if i multiply by 24 i get a negative value:
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:

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.  Register To Reply

2. ## 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.  Register To Reply

3. ## 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  Register To Reply

4. ## 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.  Register To Reply

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