Got a credit card? use our Credit Card & Finance Calculators
Thanks to Anonymous,bruncher,niord,gvonge,Shelford, for Donating to support the site
Excel formula please
-
- Lemon Quarter
- Posts: 2890
- Joined: November 6th, 2016, 9:58 pm
- Has thanked: 1408 times
- Been thanked: 3838 times
Re: Excel formula please
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!
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!
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Excel formula please
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...
Not sure what this says about Excel, or programming in general
More than one way to de-pelt a domestic feline quadruped I guess...
-
- Lemon Slice
- Posts: 634
- Joined: March 22nd, 2017, 10:17 am
- Has thanked: 112 times
- Been thanked: 226 times
Re: Excel formula please
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.
-
- Lemon Slice
- Posts: 634
- Joined: March 22nd, 2017, 10:17 am
- Has thanked: 112 times
- Been thanked: 226 times
Re: Excel formula please
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.
-
- Lemon Quarter
- Posts: 4131
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3284 times
- Been thanked: 2868 times
Re: Excel formula please
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
-
- Lemon Slice
- Posts: 634
- Joined: March 22nd, 2017, 10:17 am
- Has thanked: 112 times
- Been thanked: 226 times
Re: Excel formula please
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.
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Excel formula please
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!
-
- Lemon Quarter
- Posts: 2890
- Joined: November 6th, 2016, 9:58 pm
- Has thanked: 1408 times
- Been thanked: 3838 times
Re: Excel formula please
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.
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Excel formula please
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..)
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..)
-
- Lemon Quarter
- Posts: 2119
- Joined: November 4th, 2016, 9:40 am
- Has thanked: 1049 times
- Been thanked: 859 times
Re: Excel formula please
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.
-
- Lemon Slice
- Posts: 634
- Joined: March 22nd, 2017, 10:17 am
- Has thanked: 112 times
- Been thanked: 226 times
Re: Excel formula please
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.
-
- Lemon Half
- Posts: 8538
- Joined: November 8th, 2016, 5:56 am
- Has thanked: 4537 times
- Been thanked: 3668 times
Re: Excel formula please
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..
Code: Select all
=40 + IF(C5>=1000,30,0) + IF(C5>=10000,30,0)
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
Return to “Technology - Computers, TV, Phones etc.”
Who is online
Users browsing this forum: londoninvestor and 22 guests