Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Excel formula please

Seek assistance with all types of tech. - computer, phone, TV, heating controls etc.
Clitheroekid
Lemon Quarter
Posts: 2856
Joined: November 6th, 2016, 9:58 pm
Has thanked: 1384 times
Been thanked: 3771 times

Re: Excel formula please

#462909

Postby Clitheroekid » December 3rd, 2021, 3:44 pm

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! :D

AleisterCrowley
Lemon Half
Posts: 6381
Joined: November 4th, 2016, 11:35 am
Has thanked: 1880 times
Been thanked: 2026 times

Re: Excel formula please

#462916

Postby AleisterCrowley » December 3rd, 2021, 4:08 pm

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...

nmdhqbc
Lemon Slice
Posts: 634
Joined: March 22nd, 2017, 10:17 am
Has thanked: 112 times
Been thanked: 226 times

Re: Excel formula please

#462923

Postby nmdhqbc » December 3rd, 2021, 4:28 pm

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
Lemon Slice
Posts: 634
Joined: March 22nd, 2017, 10:17 am
Has thanked: 112 times
Been thanked: 226 times

Re: Excel formula please

#462928

Postby nmdhqbc » December 3rd, 2021, 4:43 pm

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
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: Excel formula please

#462953

Postby kiloran » December 3rd, 2021, 6:00 pm

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

nmdhqbc
Lemon Slice
Posts: 634
Joined: March 22nd, 2017, 10:17 am
Has thanked: 112 times
Been thanked: 226 times

Re: Excel formula please

#462954

Postby nmdhqbc » December 3rd, 2021, 6:03 pm

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.

AleisterCrowley
Lemon Half
Posts: 6381
Joined: November 4th, 2016, 11:35 am
Has thanked: 1880 times
Been thanked: 2026 times

Re: Excel formula please

#462955

Postby AleisterCrowley » December 3rd, 2021, 6:12 pm

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!

Clitheroekid
Lemon Quarter
Posts: 2856
Joined: November 6th, 2016, 9:58 pm
Has thanked: 1384 times
Been thanked: 3771 times

Re: Excel formula please

#462967

Postby Clitheroekid » December 3rd, 2021, 7:26 pm

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.

AleisterCrowley
Lemon Half
Posts: 6381
Joined: November 4th, 2016, 11:35 am
Has thanked: 1880 times
Been thanked: 2026 times

Re: Excel formula please

#462968

Postby AleisterCrowley » December 3rd, 2021, 7:32 pm

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..)

GrahamPlatt
Lemon Quarter
Posts: 2059
Joined: November 4th, 2016, 9:40 am
Has thanked: 1032 times
Been thanked: 823 times

Re: Excel formula please

#462970

Postby GrahamPlatt » December 3rd, 2021, 7:49 pm

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! :D


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

Outstanding resource we have here.

Thank you one and all.

nmdhqbc
Lemon Slice
Posts: 634
Joined: March 22nd, 2017, 10:17 am
Has thanked: 112 times
Been thanked: 226 times

Re: Excel formula please

#462976

Postby nmdhqbc » December 3rd, 2021, 8:06 pm

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.

servodude
Lemon Half
Posts: 8271
Joined: November 8th, 2016, 5:56 am
Has thanked: 4435 times
Been thanked: 3564 times

Re: Excel formula please

#463016

Postby servodude » December 4th, 2021, 6:34 am

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: No registered users and 18 guests