Ignoring #VALUE! Entries

[fearonc]

I am having difficulty devising a formula syntax that will ignore including #VALUE! entries. See attached file.

To illustrate, I have placed -1 values in several cells Column B. This allows the formulas in Columns S and T to work. However, as soon as you put a value of zero in column B, all entries in Column S and T become #VALUE

If Column Q displays #VALUE!, it is because it is feeding off a “0" entry in Column B. Change a zero amount in column B to a positive or negative integer and the Q cells populates correctly.

Is there a way to restructure formulas in Columns S and T to allow for “0" entry in Column B?

Thanks!!

[FDibbins]

Wrap the formulas in Q in IFERROR()
=IFERROR(RANK(N3,$N$3:$N$14,0)+(SUMPRODUCT(--(O3>$O$3:$O$14))+1)/10^6,"")

Use this same method for the S and T

[martindwilson]

before we go there why do you think aug is greater than jun? since you are comparing text jul is >aug
=O5>O3 ie =aug>jun = false

[fearonc]

Perfect!!! Thanks!

[Pan314]

Hi fearonc

Committed the error in the column G and Mon. Because of division by 0, overflow.
This is corrected you, so that the result is replaced wrote "". This caused other calculation error.

I see now that the subject has been solved, but I will send to you.

Pan314

If the information was helpful, hit a button * below.