Function doesn't pick up certain cell reference

[Guest]

Hi there,
I have a question about the "cell reference" in Excel.
I wonder why Excel doesn't pick up the cell reference that I enter in my
fomula
(=IF(OR(AA8="X",AA11:AA49="C"),"",IF(AA11:AA49="C","",$A$50-COUNTIF(AA11:AA49,"X")-COUNTIF(AA11:AA49,"N")-COUNTIF(AA11:AA49,"C")-COUNTIF(AA11:AA49,"A")-COUNTIF(AA11:AA49,"AE")-COUNTIF(AA11:AA49,"B")-COUNTIF(AA11:AA49,"BE")-COUNTIF(AA11:AA49,"LTL")))

Excel doesn't pick up (OR(AA8="X",AA11:AA49="C") this part at all. I think
the rest of the formular is fine. But when I try (ORAA8="X",AA11="C"), the
formular works fine.

Excel gives me (#VALUE!) error when I enter it as a cell range (AA11:AA49),
instead of one cell (AA11)

Does anyone have any idea of how to fix this, or what is wrong with my
formular?
Any info/clue are appreciated!!!!!

Neon520

 

[Guest]

Hello Neon520,

I don't think you can enter that formula in a range. Perhaps you can explain
in words what you are trying to do

 

[Guest]

Hi there,
What I am trying to do is: if cell AA8 is x OR if cell AA11:AA49 are C,
please calculate as follow, but unfortunately, Excel doesn't pick up the
formular when I enter the cell range AA11:AA49.
However, when I try to put only one cell (AA11), the formular works just fine.

And the thing is I also tried a dummy test that I basically use the same
type of formular (but less complex calculation at the end), the formular also
works fine.

I wonder what went wrong? Doesn't Excel pick the original formular because
it is AA, or because the cell range (AA11:AA49) related to something else in
the sheet that might cause this complication/error?

Neon520

 

[Guest]

You can only use the construction IF(AA11:AA49="C", in a so-called "array
formula" where the next part matches, e.g. in a formula like

=SUM(IF(AA11:AA49="C",BB11:BB49))

In your formula the $A$50-COUNTIF...etc part gives a single value so this
isn't valid..
I still can't quite work out what you want to do. Can you explain what you
want the result to be in a single cell?

 

[Guest]

Hi there again,

I don't think the end part (value_if_false) is the cause of this problem.
Let's say if you want to calculate: if cell AA8 is "X" OR if cell range
AA11:AA49 are "C", please say "YES" other wise say "NO". And the formular
for this would be: =IF(OR(AA3="X",AA11:AA49="C"),"YES","NO")

Is this the correct formular to do so?
If not, what should I do to get the result I want?

Anyway, do you know what function should I use if want to calculate a
certain cell range, but ONLY IF they contain value, otherwise the cell blank.
I don't want to put all SUM function, because when there are no value, it'll
come out as "0"

Thanks,
Neon520

 

[Guest]

You can use that formula if you confirm with CTRL+SHIFT+ENTER but, as I said
before, it is only valid for a single cell.

In your original formula you have another check for AA11=AA49="C". I'm not
sure you need both, and the second would also need to be enclosed in an OR
function. I'm not sure it matches your intent but perhaps you could use this
formula:


=IF(AA8="X","",IF(OR(AA11:AA49="C"),"",$A$50-SUM(COUNTIF(AA11:AA49,{"X","N","C","A","AE","B","BE","LTL"}))))

confirmed with CTRL+SHIFT+ENTER

 

[Guest]

THANK YOU VERY MUCH FOR YOUR HELP.

Anyway, do you know what function should I use if want to calculate a
certain cell range, but ONLY IF they contain value, otherwise leave the
calculated cell blank.
I don't want to put all SUM function, because when there are no value, it'll
come out as "0"

If you don't mind, can I ask another question? Are an Excel profession? If
not, where do you learn all these from? Book? website? school?
How can I do to excel/major in Excel?

Neon520

 

[Guest]

If you want to sum A1:A10 but only if there is a non-zero rexult you could use


=IF(SUM(A1:A10),SUM(A1:A10),"")

No, I'm not an Excel professional. I use Excel for my work and use it for
various projects in my spare time. I've never read an excel book, everything
I know I've picked up by using excel and from posting and lurking on this
forum and others........I recommend it!!

 

[Guest]

Wow, you must be really good at computer. You didn't even read book and you
know all these.....
I agree that this is a good forum to learn about office application.
I am look forward to learn a lot more about Office aplication.
Again, thank you very much for you help.

Neon520