ROUND a range of values within a COUNTIF before comparison

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
474
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi folks,
I have this formula:
=IF($D$6=$D$26,"n/a",COUNTIF($B$9:$V$18,">"&D7))


I want to convert this so it ROUNDS all cells in the range $B$9:$V$18 before it checks if it is > D7.
The values can be any precision between 1 and 6 places of decimals which is dictated by the value in G7.
So something like:
=IF($D$6=$D$26,"n/a",COUNTIF(ROUND($B$9,$G$7):ROUND($V$18,$G$7),">"&D7))

But this produces an error, error in formula not result, so I'm assuming that syntax is just wrong.

Thanks.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi, you can't do that with COUNTIF() - you would need to switch to alternative, of which one is:

=IF($D$6=$D$26,"n/a",SUMPRODUCT(--(ROUND($B$9:$V$18,G7)>D7)))
 
Upvote 0
That returns the #VALUE ! error.

One thing I have noticed.
When B9 = 3.1
=ROUND(B9,1) results in 3
=ROUND(B9,2) results in 3
=ROUND(B9,3) results in 3

It appears to be ignoring the ROUND()
 
Upvote 0
When B9 = 3.1
=ROUND(B9,1) results in 3
=ROUND(B9,2) results in 3
=ROUND(B9,3) results in 3
I don't think so. I think you probably just have the formatting on the cell set up to display no decimals!
Try changing the cell format to General, which has no restrictions on decimal places.
 
Upvote 0
You are correct. I just realised that.
Any ideas why your formula would be returning the #VALUE ! error?
 
Upvote 0
Do you have any errors or non-numeric values in any of the ranges being referenced by the formula?
 
Upvote 0
Yes gentlemen, yes I do.
That would have been a useful bit of information to have provided up front. Not thinking today.
The range B9:V18 is prepopulated with n/a.
Then values are entered into only the cells required. The rest will remain n/a.
 
Upvote 0
That could prove to be a bit problematic, as you cannot round text values.
Do they need to be "n/a"? Why not just leave them blank?
 
Upvote 0
It's someone else's report and there are other things tied into the n/a in those cells.
It would be a lot of work to redesign it.
I have an idea for a long winded way around it, not sure if it will work.
A formula like FormR provided would have been Sooooo much more efficient.
But thanks a lot to both of you for having a go.
Much appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top