Value used is wrong data type..

D

Destrachan

Here is a copy of the formula that I *thought* would work...lol

=IF(SUM($X5,$B5,$C5)=$AK5,D5/(L5-(SUM(B5:C5))),P5/(X5-(SUM(N5:O5))))

However, it returns a #VALUE error. I'm quite confused with the error
as all the cells that are referrenced are formatted using the same
type. Admittedly I don't know anything about arrays, but I did try
entering the formula using Ctrl+Shift+Enter and got the same end
result.

If you need any information about what it's being used for, let me
know.

Any help would be greatly appreciated. :)
 
D

Destrachan

Here is a copy of the formula that I *thought* would work...lol

=IF(SUM($X5,$B5,$C5)=$AK5,D5/(L5-(SUM(B5:C5))),P5/(X5-(SUM(N5:O5))))

However, it returns a #VALUE error.  I'm quite confused with the error
as all the cells that are referrenced are formatted using the same
type.  Admittedly I don't know anything about arrays, but I did try
entering the formula using Ctrl+Shift+Enter and got the same end
result.

If you need any information about what it's being used for, let me
know.

Any help would be greatly appreciated. :)

Well, I believe I've narrowed the problem. The cells that are being
referrenced are either summed OR they're being pulled from another
worksheet, so there's formulas, but effectivly the cells are blank. I
set the formula that pulls the data to display a 0 (zero) if it's
blank and that got everything working properly. So either I'm going
to have to have a bunch of 0's on my sheet...unless anyone's able to
provide/suggest a work around?
 
R

Ron Rosenfeld

Here is a copy of the formula that I *thought* would work...lol

=IF(SUM($X5,$B5,$C5)=$AK5,D5/(L5-(SUM(B5:C5))),P5/(X5-(SUM(N5:O5))))

However, it returns a #VALUE error. I'm quite confused with the error
as all the cells that are referrenced are formatted using the same
type. Admittedly I don't know anything about arrays, but I did try
entering the formula using Ctrl+Shift+Enter and got the same end
result.

If you need any information about what it's being used for, let me
know.

Any help would be greatly appreciated. :)

The issue is most likely with your data. Formatting Excel cells
only changes how certain values are displayed. It does NOT change the
type of data in the cell.

You need to look at the precedents and contents to figure this out.
 
M

Ms-Exl-Learner

Instead of Number you might have entered the Text Character in D5 or
L5 or P5 or X5. Check these cells values. These cells only will
cause the formula to return #VALUE when there is a Text input instead
numbers. And at the same time it is not an array formula, so general
enter is enough to run this formula perfectly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top