Forum Discussion
Inconsistent excel formula
In the file attached below, I am facing an error of inconsistent formula for column J. I tried to fix it but I am not able to. Any help would be appreciated. TIA
9 Replies
- I also believe that the 'error' can be ignored. Clear the error by using the option in presented in the red corner handle.
PRANAY1395 wrote: ``I am facing an error of inconsistent formula for column J.``
PRANAY1395 wrote: ``I am not able to figure out. Can you fix and send me the file?``
PRANAY1395 wrote: ``I tried to solve. But it still shows the same.``
THERE IS __NO__ FUNCTIONAL INCONSISTENCY IN COLUMN J !!
Forgive me for "shouting", but you seem to be "hard of hearing", ignoring what HansVogelaar has told you twice.
To demonstrate, select J3:J18 and temporarily move it to M3:M18. Note that the "errors" go away. To be sure, click Formula > Error Checking. Note that Excel reports that there are no "errors".
(Move M3:M18 back to J3:J18. Or simply close and reopen the original file.)
The simplest "fix" is to disable the option.
Select J3, click the "!" warning to the left, and click Error Checking Options. Deselect (click to remove the checkmark) the option "Enable background error checking".
Also click Reset Ignored Errors to make the pre-existing warnings to go away.
Alternatively, if you prefer to keep the option enabled (why?! obviously, it confuses you), select J3:J18, click the "!" warning on the left, and click Ignore Error.
But that is only temporary. The warning returns if and when you edit those cells later, and when you perform Formula > Error Checking.
-----
Aside.... There __is__ an inconsistency in the form, but not the operation, of the formula in J3 and the formulas in J4:J18. It would be nice to make them consistent.
Copy J4 and paste into J3:J18.
------
Aside.... But the syntax of your formulas are unnecessarily complicated. The worst example: ((B$3)) !! OMG! "How many parentheses does it take to screw in a lightbulb?" (wink; a programmer's "in" joke)
The formula in J4 can be written more tersely (and clearly, IMHO) as follows:
=(1+H5)^(B4+1) / (1+H4)^B4 - 1
or if you insist:
=( ((1+H5)^(B4+1)) / ((1+H4)^B4) ) - 1
Then copy J4 and paste into J3:J18.
In the second form, the red parentheses around subexpressions are unnecessary. But there is a "school of thought" that likes to "fully parenthesize" subexpressions instead of relying on "operator precedence" (default order of operations). Those are always the formulas that people come to us later to ask "WTF is this formula doing?", usually because they are so unreadable.
-----
Explanation of the inconsistency "errors"....
First, the term "error" is a misnomer here. They are warnings.
Second, the warnings are only guesses by Excel, based on the form of formulas in adjacent cells.
Third, Excel is usually wrong.
Finally, the "inconsistency" that Excel seems to complain about is obviously intentional and irrelevant.
It seems to be complaining because the formulas in column J are different from column I. Well duh! They do different things. Klunk!
I reach that conclusion by selecting J3, then clicking Formula > Error Checking. The pop-up dialog box has a button labeled Copy Formula From Left.
But __you__ know that is nonsense! The formulas in column J are not intended to be the same as the formulas in column I. Functionally, apples and oranges! GIGO!
That is why I routinely disable Excel's formula "error checking". In fact, I disable all of the options where Excel asserts its (mis)judgment over mine. For example, AutoCorrect.
There might indeed be some functional errors in some of your formulas. But I am not going to risk misdirecting you by delving into a design that I am not taking the time to understand.
Look at the trace precedents, everything from the bottom going up is consistent until you get to the top:
Yea_So I am attaching the file I tried to solve. But it still shows the same.
How can I make corrections when I don't know the concept behind the calculations?
However, I will show you the differences between the calculations on each column so you may figure you where you might have made a mistake in your calculations:
for example:
check if these calculations are correct.
Price
E
= D20/(1+(D3/2))^B3
= D20/(1+(D4/2))^B4
=((D20*(C5/2))/(1+(D5/2))^B3)+((D20*(C5/2))/(1+(D5/2))^B4)+((D20+(D20*(C5/2)))/(1+(D5/2))^B5)
=((D20*(C6/2))/(1+(D6/2))^B3)+((D20*(C6/2))/(1+(D6/2))^B4)+((D20*(C6/2))/(1+(D6/2))^B5)+((D20+(D20*(C6/2)))/(1+(D6/2))^B6)
=((D20*(C7/2))/(1+(D7/2))^B3)+((D20*(C7/2))/(1+(D7/2))^B4)+((D20*(C7/2))/(1+(D7/2))^B5)+((D20*(C7/2))/(1+(D7/2))^B6)+((D20+(D20*(C7/2)))/(1+(D7/2))^B7)
=((D20*(C8/2))/(1+(D8/2))^B3)+((D20*(C8/2))/(1+(D8/2))^B4)+((D20*(C8/2))/(1+(D8/2))^B5)+((D20*(C8/2))/(1+(D8/2))^B6)+((D20*(C8/2))/(1+(D8/2))^B7)+((D20+(D20*(C8/2)))/(1+(D8/2))^B8)
=((D20*(C9/2))/(1+(D9/2))^B3)+((D20*(C9/2))/(1+(D9/2))^B4)+((D20*(C9/2))/(1+(D9/2))^B5)+((D20*(C9/2))/(1+(D9/2))^B6)+((D20*(C9/2))/(1+(D9/2))^B7)+((D20*(C9/2))/(1+(D9/2))^B8)+((D20+(D20*(C9/2)))/(1+(D9/2))^B9)
=((D20*(C10/2))/(1+(D10/2))^B3)+((D20*(C10/2))/(1+(D10/2))^B4)+((D20*(C10/2))/(1+(D10/2))^B5)+((D20*(C10/2))/(1+(D10/2))^B6)+((D20*(C10/2))/(1+(D10/2))^B7)+((D20*(C10/2))/(1+(D10/2))^B8)+((D20*(C10/2))/(1+(D10/2))^B9)+((D20+(D20*(C10/2)))/(1+(D10/2))^B10)
=((D20*(C11/2))/(1+(D11/2))^B3)+((D20*(C11/2))/(1+(D11/2))^B4)+((D20*(C11/2))/(1+(D11/2))^B5)+((D20*(C11/2))/(1+(D11/2))^B6)+((D20*(C11/2))/(1+(D11/2))^B7)+((D20*(C11/2))/(1+(D11/2))^B8)+((D20*(C11/2))/(1+(D11/2))^B9)+((D20*(C11/2))/(1+(D11/2))^B10)+((D20+(D20*(C11/2)))/(1+(D11/2))^B11)
=((D20*(C12/2))/(1+(D12/2))^B3)+((D20*(C12/2))/(1+(D12/2))^B4)+((D20*(C12/2))/(1+(D12/2))^B5)+((D20*(C12/2))/(1+(D12/2))^B6)+((D20*(C12/2))/(1+(D12/2))^B7)+((D20*(C12/2))/(1+(D12/2))^B8)+((D20*(C12/2))/(1+(D12/2))^B9)+((D20*(C12/2))/(1+(D12/2))^B10)+((D20*(C12/2))/(1+(D12/2))^B11)+((D20+(D20*(C12/2)))/(1+(D12/2))^B12)
=((D20*(C13/2))/(1+(D13/2))^B3)+((D20*(C13/2))/(1+(D13/2))^B4)+((D20*(C13/2))/(1+(D13/2))^B5)+((D20*(C13/2))/(1+(D13/2))^B6)+((D20*(C13/2))/(1+(D13/2))^B7)+((D20*(C13/2))/(1+(D13/2))^B8)+((D20*(C13/2))/(1+(D13/2))^B9)+((D20*(C13/2))/(1+(D13/2))^B10)+((D20*(C13/2))/(1+(D13/2))^B11)+((D20*(C13/2))/(1+(D13/2))^B12)+((D20+(D20*(C13/2)))/(1+(D13/2))^B13)
=((D20*(C14/2))/(1+(D14/2))^B3)+((D20*(C14/2))/(1+(D14/2))^B4)+((D20*(C14/2))/(1+(D14/2))^B5)+((D20*(C14/2))/(1+(D14/2))^B6)+((D20*(C14/2))/(1+(D14/2))^B7)+((D20*(C14/2))/(1+(D14/2))^B8)+((D20*(C14/2))/(1+(D14/2))^B9)+((D20*(C14/2))/(1+(D14/2))^B10)+((D20*(C14/2))/(1+(D14/2))^B11)+((D20*(C14/2))/(1+(D14/2))^B12)+((D20*(C14/2))/(1+(D14/2))^B13)+((D20+(D20*(C14/2)))/(1+(D14/2))^B14)
=((D20*(C15/2))/(1+(D15/2))^B3)+((D20*(C15/2))/(1+(D15/2))^B4)+((D20*(C15/2))/(1+(D15/2))^B5)+((D20*(C15/2))/(1+(D15/2))^B6)+((D20*(C15/2))/(1+(D15/2))^B7)+((D20*(C15/2))/(1+(D15/2))^B8)+((D20*(C15/2))/(1+(D15/2))^B9)+((D20*(C15/2))/(1+(D15/2))^B10)+((D20*(C15/2))/(1+(D15/2))^B11)+((D20*(C15/2))/(1+(D15/2))^B12)+((D20*(C15/2))/(1+(D15/2))^B13)+((D20*(C15/2))/(1+(D15/2))^B14)+((D20+(D20*(C15/2)))/(1+(D15/2))^B15)
=((D20*(C16/2))/(1+(D16/2))^B3)+((D20*(C16/2))/(1+(D16/2))^B4)+((D20*(C16/2))/(1+(D16/2))^B5)+((D20*(C16/2))/(1+(D16/2))^B6)+((D20*(C16/2))/(1+(D16/2))^B7)+((D20*(C16/2))/(1+(D16/2))^B8)+((D20*(C16/2))/(1+(D16/2))^B9)+((D20*(C16/2))/(1+(D16/2))^B10)+((D20*(C16/2))/(1+(D16/2))^B11)+((D20*(C16/2))/(1+(D16/2))^B12)+((D20*(C16/2))/(1+(D16/2))^B13)+((D20*(C16/2))/(1+(D16/2))^B14)+((D20*(C16/2))/(1+(D16/2))^B15)+((D20+(D20*(C16/2)))/(1+(D16/2))^B16)
=((D20*(C17/2))/(1+(D17/2))^B3)+((D20*(C17/2))/(1+(D17/2))^B4)+((D20*(C17/2))/(1+(D17/2))^B5)+((D20*(C17/2))/(1+(D17/2))^B6)+((D20*(C17/2))/(1+(D17/2))^B7)+((D20*(C17/2))/(1+(D17/2))^B8)+((D20*(C17/2))/(1+(D17/2))^B9)+((D20*(C17/2))/(1+(D17/2))^B10)+((D20*(C17/2))/(1+(D17/2))^B11)+((D20*(C17/2))/(1+(D17/2))^B12)+((D20*(C17/2))/(1+(D17/2))^B13)+((D20*(C17/2))/(1+(D17/2))^B14)+((D20*(C17/2))/(1+(D17/2))^B15)+((D20*(C17/2))/(1+(D17/2))^B16)+((D20+(D20*(C17/2)))/(1+(D17/2))^B17)
=((D20*(C18/2))/(1+(D18/2))^B3)+((D20*(C18/2))/(1+(D18/2))^B4)+((D20*(C18/2))/(1+(D18/2))^B5)+((D20*(C18/2))/(1+(D18/2))^B6)+((D20*(C18/2))/(1+(D18/2))^B7)+((D20*(C18/2))/(1+(D18/2))^B8)+((D20*(C18/2))/(1+(D18/2))^B9)+((D20*(C18/2))/(1+(D18/2))^B10)+((D20*(C18/2))/(1+(D18/2))^B11)+((D20*(C18/2))/(1+(D18/2))^B12)+((D20*(C18/2))/(1+(D18/2))^B13)+((D20*(C18/2))/(1+(D18/2))^B14)+((D20*(C18/2))/(1+(D18/2))^B15)+((D20*(C18/2))/(1+(D18/2))^B16)+((D20*(C18/2))/(1+(D18/2))^B17)+((D20+(D20*(C18/2)))/(1+(D18/2))^B18)check these formulas in column E are they correct?
Discount
Factor
F
=1/(1+D3/2)^B3
=1/(1+D4/2)^B4
=+(E5 - (( C5 / 2 * $D$20 ) * ( G4 ))) / ( $D$20 + ( $D$20 * C5 / 2 ))
=+(E6 - (( C6 / 2 * $D$20 ) * ( G5 ))) / ( $D$20 + ( $D$20 * C6 / 2 ))
=+(E7 - (( C7 / 2 * $D$20 ) * ( G6 ))) / ( $D$20 + ( $D$20 * C7 / 2 ))
=+(E8 - (( C8 / 2 * $D$20 ) * ( G7 ))) / ( $D$20 + ( $D$20 * C8 / 2 ))
=+(E9 - (( C9 / 2 * $D$20 ) * ( G8 ))) / ( $D$20 + ( $D$20 * C9 / 2 ))
=+(E10 - (( C10 / 2 * $D$20 ) * ( G9 ))) / ( $D$20 + ( $D$20 * C10 / 2 ))
=+(E11 - (( C11 / 2 * $D$20 ) * ( G10 ))) / ( $D$20 + ( $D$20 * C11 / 2 ))
=+(E12 - (( C12 / 2 * $D$20 ) * ( G11 ))) / ( $D$20 + ( $D$20 * C12 / 2 ))
=+(E13 - (( C13 / 2 * $D$20 ) * ( G12 ))) / ( $D$20 + ( $D$20 * C13 / 2 ))
=+(E14 - (( C14 / 2 * $D$20 ) * ( G13 ))) / ( $D$20 + ( $D$20 * C14 / 2 ))
=+(E15 - (( C15 / 2 * $D$20 ) * ( G14 ))) / ( $D$20 + ( $D$20 * C15 / 2 ))
=+(E16 - (( C16 / 2 * $D$20 ) * ( G15 ))) / ( $D$20 + ( $D$20 * C16 / 2 ))
=+(E17 - (( C17 / 2 * $D$20 ) * ( G16 ))) / ( $D$20 + ( $D$20 * C17 / 2 ))
=+(E18 - (( C18 / 2 * $D$20 ) * ( G17 ))) / ( $D$20 + ( $D$20 * C18 / 2 ))Calculations in column F check their correctness
Sum of current
and prior period
discount factors
G
=+F3
=+F4+F3
=G4+F5
=G5+F6
=G6+F7
=G7+F8
=G8+F9
=G9+F10
=G10+F11
=G11+F12
=G12+F13
=G13+F14
=G14+F15
=G15+F16
=G16+F17
=G17+F18Column G
Theoretical
Semi-Annual
Spot Rate
H
=(+D3/2*100)%
=+D4/2
=(1/F5^(1/B5))-1
=(1/F6^(1/B6))-1
=(1/F7^(1/B7))-1
=(1/F8^(1/B8))-1
=(1/F9^(1/B9))-1
=(1/F10^(1/B10))-1
=(1/F11^(1/B11))-1
=(1/F12^(1/B12))-1
=(1/F13^(1/B13))-1
=(1/F14^(1/B14))-1
=(1/F15^(1/B15))-1
=(1/F16^(1/B16))-1
=(1/F17^(1/B17))-1
=(1/F18^(1/B18))-1Column H
Theoretical
Annual
Spot Rate
I
=+H3*2
=+H4*2
=+H5*2
=+H6*2
=+H7*2
=+H8*2
=+H9*2
=+H10*2
=+H11*2
=+H12*2
=+H13*2
=+H14*2
=+H15*2
=+H16*2
=+H17*2
=+H18*2Column I
Implied
Semi-Annual
Forward Rates
J
=+(((1+H$4)^(B$4)/(1+H$3)^((B$3))-1))
=+(((1+H5)^(B4+1)/(1+H4)^(B4))-1)
=+(((1+H6)^(B5+1)/(1+H5)^(B5))-1)
=+(((1+H7)^(B6+1)/(1+H6)^(B6))-1)
=+(((1+H8)^(B7+1)/(1+H7)^(B7))-1)
=+(((1+H9)^(B8+1)/(1+H8)^(B8))-1)
=+(((1+H10)^(B9+1)/(1+H9)^(B9))-1)
=+(((1+H11)^(B10+1)/(1+H10)^(B10))-1)
=+(((1+H12)^(B11+1)/(1+H11)^(B11))-1)
=+(((1+H13)^(B12+1)/(1+H12)^(B12))-1)
=+(((1+H14)^(B13+1)/(1+H13)^(B13))-1)
=+(((1+H15)^(B14+1)/(1+H14)^(B14))-1)
=+(((1+H16)^(B15+1)/(1+H15)^(B15))-1)
=+(((1+H17)^(B16+1)/(1+H16)^(B16))-1)
=+(((1+H18)^(B17+1)/(1+H17)^(B17))-1)Column J
Implied
Annual
Forward Rates
K
=+J3*2
=+J4*2
=+J5*2
=+J6*2
=+J7*2
=+J8*2
=+J9*2
=+J10*2
=+J11*2
=+J12*2
=+J13*2
=+J14*2
=+J15*2
=+J16*2
=+J17*2Column K
Ask yourself this, Why do some rows not have consistent formulas?
cheers
- I am not able to figure out. Can you fix and send me the file?
In general, you can ignore this.
But I'd look closely at the formulas in J3 and J4. Compare them carefully.
The formula in J3 refers to two cells in column B. The other formulas refer only to one cell in column B.
