[Closed] Whats wrong with this formula?

That's two different formulae. Is the whole thing in one cell?

Sorry, one to return a rounded up percentage range to allow for Pivot filters from 10-100+, and the other less ranges

That it's horrible?

What exactly are you trying to do?

Also missing loads of and statements isn't it?

Can you just use this:
=IF(W2>100,"100+",ROUNDUP(W2,-1))

Edit:

That it’s horrible?

Well, yes!

Edit 2: I've had another look at it, there's so much unnecessary complication there.

e.g. the first chunk of it =IF(W2<=0.1,“0”,IF(W2>=0.1,W2<=9.99,”
The first if says if it's less than or equal to 0.1 then show zero. After the second comma should be the "if thats's not true" value. But you've got another if, to check if it's greater than or equal to 0.1, except it must be greater than 0.1 because it's failed the first part of the IF statement.

I think you'd need to use AND() to have multiple criteria in the IF. Except you don't need multiple criteria, because if it's failed the first check of "<=0.1" then it must be ">0.1" so you don't need to check again.

Too many IFs

You can only nest seven in one formula IIRC

=if(w2>100,"100+",if(w2<0.1,0,roundup(w2/10,0)*10))

Should I think achieve what you're after

[edit]

You can only nest seven in one formula IIRC

14 I thought? but yeah, there's a limit

The fact that you can't tell what's wrong, well no-one will be able to quickly, says that you need to use a different function e.g. Vlookup rather than nested ifs.

You can only nest seven in one formula IIRC

14 I thought? but yeah, there’s a limit

It depends on the version. Prearchy is showing his age with the limit of 7. I think the current limit is 64 although frankly that's just stupid.

dangeourbrain
=if(w2>100,”100+”,if(w2<0.1,0,roundup(w2/10,0)*10))

Should I think achieve what you’re after

you can use "-1" in the ROUND/ROUNDUP/ROUNDDOWN formula to round to the nearest 10, -2 to 100, -3 to 1000 etc, no need to divide by 10 and multiply back up.

Ahh, learn something new! Ta for that.

Though invariably I only have to do that sort of nonsense because people work in multiples of 25.4...

Perchy is showing his age with the limit of 7.

This database was all just empty fields when I were a lad

What's wrong with it? It's an impenetrable mess. Top programming tip: if something ends up hideously complicated you're using the wrong approach. There'll be a better method, go find it.

"=IF(W2<=0.1,“0”,IF(W2>=0.1,W2<=9.99,”10”,...."

As well as being terminally ugly, the 2nd parameter of the IF function (i.e. the bit in bold) should be the TRUE return value. (I suspect you intended IF(((W2>=0.1) AND (W2<=9.99)),...,...)

But as above, there are much, much neater ways of getting what you want.

(I suspect you intended IF(((W2>=0.1) AND (W2<=9.99

Would be if(and(var1, var2, etc), true, false)

Ok, i admit it, I need your help 🙂

In essence I have a range of varying values from 72.73% to 120% etc, I'm looking to set ranges that I can then just filter by, such as 0%, 25%, 50%, 75% 100% and 100+

=IF(AND(W2>=0.1,W2<=24.99),"25",IF(AND(W2>=25,W2<=49.99),"50", IF(AND(W2>=50,W2<=74.99),"75", IF(AND(W2>=75,W2<=99.99),"100",""))))

Hi, decided to just use this, much easier, thanks for your help 🙂

=ROUND(W2,1)

Though invariably I only have to do that sort of nonsense because people work in multiples of 25.4…

So inches to millimetres then? You could just run it in inches and then convert to mm's at the end 😉

Try a vbscript. Anything that complicated in one cell will be impossible to alter / maintain in the future.

The first if says if it’s less than or equal to 0.1 then show zero. After the second comma should be the “if thats’s not true” value. But you’ve got another if, to check if it’s greater than or equal to 0.1, except it must be greater than 0.1 because it’s failed the first part of the IF statement.

Excel formulae construction and all other things aside, the core logic is screwed here because one of those two things shouldn't be an "or equals."

If you can't get a formula to get what you want (the with the rounding etc), look at the switch function instead of nested ifs.

It's much easier to get right and work out what it was meant to do when you look at it again a few days (minutes?) later.

I'll tell you whats wrong with it- no mention of a Skoda or a wood burner. Bloody idiots.