The Lemon Fool

Although I'd said TIA for any assistance I really felt I had to say thank you for so many helpful answers. And I'm impressed that there are so many of you who are able to solve what was to me an insuperable problem with such ease. I guess it helps explain why I chose law rather than accountancy!

I ended up using kiloran's formula, only because it had been endorsed by NotSure, and it worked perfectly, though I'm sure the others would have done so as well.

Thanks again, you've made an old man very happy!

Interesting to see so many different answers, most(all?) of which probably work fine (I haven't checked any, other than mine)
Not sure what this says about Excel, or programming in general
More than one way to de-pelt a domestic feline quadruped I guess...

Clitheroekid wrote:I ended up using kiloran's formula, only because it had been endorsed by NotSure, and it worked perfectly, though I'm sure the others would have done so as well.

look out for empty cell and non numeric inputs. it does not work for them as far as i can tell. gives 40 and 100 respectively.

nmdhqbc wrote:

Clitheroekid wrote:I ended up using kiloran's formula, only because it had been endorsed by NotSure, and it worked perfectly, though I'm sure the others would have done so as well.

look out for empty cell and non numeric inputs. it does not work for them as far as i can tell. gives 40 and 100 respectively.

and negative numbers give 100 too. forgot to say earlier.

nmdhqbc wrote:

nmdhqbc wrote:

Clitheroekid wrote:I ended up using kiloran's formula, only because it had been endorsed by NotSure, and it worked perfectly, though I'm sure the others would have done so as well.

look out for empty cell and non numeric inputs. it does not work for them as far as i can tell. gives 40 and 100 respectively.

and negative numbers give 100 too. forgot to say earlier.

Yes, I admit I wrote it in a rush and literally translated CK's request into a formula, without looking at subtleties or alternatives. But, typical programming..... a zillion solutions to a problem (and I learnt a few new things, which is great)

But......nested IFs in excel... Ugh!!!!

--kiloran

kiloran wrote:Yes, I admit I wrote it in a rush and literally translated CK's request into a formula, without looking at subtleties or alternatives.

yeah, your warning on the original post is what prompted me to check it.

kiloran wrote:

nmdhqbc wrote:

nmdhqbc wrote:
look out for empty cell and non numeric inputs. it does not work for them as far as i can tell. gives 40 and 100 respectively.

and negative numbers give 100 too. forgot to say earlier.

Yes, I admit I wrote it in a rush and literally translated CK's request into a formula, without looking at subtleties or alternatives. But, typical programming..... a zillion solutions to a problem (and I learnt a few new things, which is great)

But......nested IFs in excel... Ugh!!!!

--kiloran

My Excel programming is straight from brain to keyboard, so I often end up with stuff that is unwieldy/sub-optimal
I have picked up a few new things from this thread, happy to say!

nmdhqbc wrote:look out for empty cell and non numeric inputs. it does not work for them as far as i can tell. gives 40 and 100 respectively.

and negative numbers give 100 too.

These aren't a possibility. The reason I wanted it is so that I could calculate the collection charges to be added to outstanding invoices when I'm collecting debts for clients. If the debt is up to £999.99 the charge is £40; if it's between £1,000 and £999.99 it's £70; and if it's over £10k then it's £100.

Consequently, there could never be an empty cell, a non-numeric input or a negative figure.

Ah, but you're talking to computer programmers here (I'm excluding myself from this elite group)
What happens if I put a negative number in?
What about a number that Excel thinks is a text string (hint...use the value() function)
What about blanks, unprintable characters, whatever ...(clean() and trim() get some use..)

Clitheroekid wrote:Although I'd said TIA for any assistance I really felt I had to say thank you for so many helpful answers. And I'm impressed that there are so many of you who are able to solve what was to me an insuperable problem with such ease.

Thanks again, you've made an old man very happy!

I should have said similar re this thread: viewtopic.php?t=27258

Outstanding resource we have here.

Thank you one and all.

Clitheroekid wrote:

nmdhqbc wrote:look out for empty cell and non numeric inputs. it does not work for them as far as i can tell. gives 40 and 100 respectively.

and negative numbers give 100 too.

These aren't a possibility. The reason I wanted it is so that I could calculate the collection charges to be added to outstanding invoices when I'm collecting debts for clients. If the debt is up to £999.99 the charge is £40; if it's between £1,000 and £999.99 it's £70; and if it's over £10k then it's £100.

Consequently, there could never be an empty cell, a non-numeric input or a negative figure.

ok, if your comfortable with the possibility of data errors messing it up. I would always make things like this full proof but each to their own.

Clitheroekid wrote:

nmdhqbc wrote:look out for empty cell and non numeric inputs. it does not work for them as far as i can tell. gives 40 and 100 respectively.

and negative numbers give 100 too.

These aren't a possibility. The reason I wanted it is so that I could calculate the collection charges to be added to outstanding invoices when I'm collecting debts for clients. If the debt is up to £999.99 the charge is £40; if it's between £1,000 and £999.99 it's £70; and if it's over £10k then it's £100.

Consequently, there could never be an empty cell, a non-numeric input or a negative figure.

Well, when you put it like that..

is how it appears in my head (I think!? I don't really Excel these days)
- still barfs on bad entry but it doesn't read with logical clashes or depend on execution order

-sd