A value used in the formula is of the wrong data type.

Try it now

Please Login or Register  to view this content.

Can you explain what you are hoping that the formula will do?

Post removed by GK

I'm not 100% clear what you want....

This array formula compares two columns, row-by-row, and returns "Yes" if, for each row, column A is less than column B.

=IF(COUNT(A1:A6)=SUM(IF(A1:A6-B1:B6<0,1,0)),"Yes","No")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

However, change cell B3 to 4. It's still higher than A3 (3), but 4 is less than some of the values in column A. In this circumstance, what do you want the formula to do?

Ok not sure I am going to make much sense here but I am hoping to get a formula that will check every row in column M on both sheets and if any number in a cell of column M on mvr_sheet is less then any cell of column M on bv_sheet then return Yes.

Don't know if that made it any clearer but it is difficult to explain things when you don't know exactly what formula one would use to arrive at the solution.

I am not trying to be difficult just don't know what you know.

Glenn Kennedy,

Looks like your solution addressed the issue, Question though is why wouldn't the way I was originally trying work? It seems you can't use $ in an IF statement as it returns #VALUE but even removing the $ it still had issues. Yeah I know the explanation may take a bit of typing so if you could point me in the right direction as for the explanation I would surely appreciate it.

Thanks for taking the time to assist me.

I'm on a bus... no PC only phone. You'll have to adapt this to suit:

=IF (MIN (cell range 1)< = MAX (cell range 2),"Yes","No")

You cannot test a range like that....
=IF(mvr_Sheet!$M$4:$M$43 < bv_Sheet!$M$4:$M$43, Yes, No)

Basically compare the numbers in $M$4:$M$43 in both sheets and if the first sheet is lower then the second sheet return Yes and if higher then return No.

what if some are lower and some are higher?

I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.