Formula to check rounding off to nearest 100

[Alvinpereira]

Hi,

Can some one please help me with this formula.

A sumproduct formula works for this check but if the data has blanks it gives me a value error. How can i get passed this.

I basically need to check a column which contains numbers, they all have to be rounded off to the nearest 100. if not give a comment in A1 as error. Problem is this column may contain blank cells which makes it difficult for me to input a formula.

SO suppose i need to input a formula in cellA1 to check for 1 lac rows, whether all the rows are filled with data or not. WHat would be the formula.

Please Help.

[TABLE="width: 150"]
<tbody>[TR]
[TD]A1[/TD]
[TD]Fine or Error[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD="align: right"]98700[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD="align: right"]76679[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD="align: right"]998800[/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

Try

=IF(COUNT(IF((A3:A6<>"")*(RIGHT(A3:A6,2)<>"00"),A3:A6)),"Error","Fine") Ctrl Shift Enter

 

[snjpverma]

what formula are you using to round off to nearest 100 ? If you tell us the formula maybe we can make some changes in that.
If you just want to check if any of the rows in A1:A100000 is blank or not
Try this array formula
Confirm it with Control + shift + Enter (not just enter)
=OR(ISBLANK(A1:A100000))

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=IF(SUM(IF(A2:A1000<>"",--RIGHT(A2:A1000,2))),"Error","Fine")

 

[Alvinpereira]

Hi All.

I have used this formula.

=IF(SUMPRODUCT(--RIGHT(A6:A55,2))=0,"Rounding off Fine","Error in rounding off")
But if i change the A6:A55 to A6:A100000 then it doesnt work even i use an array formula.

This one works =IF(COUNT(IF((A3:A6<>"")*(RIGHT(A3:A6,2)<>"00"),A3:A6)),"Error","Fine") , but i'm just cross checking still.

 

[Alvinpereira]

Hi,

This one works fine Thanks.

IF there are more formulas please do post them, might be helpfull for me later.

=IF(COUNT(IF((A3:A6<>"")*(RIGHT(A3:A6,2)<>"00"),A3:A6)),"Error","Fine")

 

[63falcondude]

Hi,

This one works fine Thanks.

IF there are more formulas please do post them, might be helpfull for me later.

=IF(COUNT(IF((A3:A6<>"")*(RIGHT(A3:A6,2)<>"00"),A3:A6)),"Error","Fine")

Great, glad to help.