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!
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
Thanks to Anonymous,bruncher,niord,gvonge,Shelford, for Donating to support the site
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.
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.
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.
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.
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
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.
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!
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.
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.
Code: Select all
=40 + IF(C5>=1000,30,0) + IF(C5>=10000,30,0)
Return to “Technology - Computers, TV, Phones etc.”
Users browsing this forum: No registered users and 25 guests