Inconsistent excel formula

Copper Contributor

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

@PRANAY1395 

The formula in J3 refers to two cells in column B. The other formulas refer only to one cell in column B.

 

@PRANAY1395 

In general, you can ignore this.

But I'd look closely at the formulas in J3 and J4. Compare them carefully.

@PRANAY1395 

 

Look at the trace precedents, everything from the bottom going up is consistent until you get to the top:

Yea_So_0-1633888094097.png

 

I am not able to figure out. Can you fix and send me the file?

@Yea_So I am attaching the file I tried to solve. But it still shows the same.

@PRANAY1395 

I wouldn't worry about that. I guess the warning is because the formula refers to the following row as well as to the row itself. But that is intentional.

You can shorten the formula to

 

=(1+H4)^B4/(1+H3)^B3-1

@PRANAY1395 

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:

Yea_So_1-1633893741650.png

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+F18 

Column 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))-1

Column 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*2

Column 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*2

Column K

 

Ask yourself this, Why do some rows not have consistent formulas?

 

cheers

 

 

@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 @Hans Vogelaar 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.

I also believe that the 'error' can be ignored. Clear the error by using the option in presented in the red corner handle.