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: 2898
- Joined: November 6th, 2016, 9:58 pm
- Has thanked: 1413 times
- Been thanked: 3842 times
Excel formula please
I've just wasted about half an hour trying to create a formula in Excel. I really have no idea what I'm doing, so I'm turning to my LF chums who can probably do it in a few seconds.
What I want the formula to do is to calculate a sum depending on the value in a given cell. In English I want it to say as follows:
If the value in cell A is between 0 and 999.99 then the result is 40; if it's between 1,000 and 9,999.99 then it's 70; and if it's 10,000 or more then the result is 100.
TIA for any assistance.
What I want the formula to do is to calculate a sum depending on the value in a given cell. In English I want it to say as follows:
If the value in cell A is between 0 and 999.99 then the result is 40; if it's between 1,000 and 9,999.99 then it's 70; and if it's 10,000 or more then the result is 100.
TIA for any assistance.
-
- 2 Lemon pips
- Posts: 249
- Joined: November 14th, 2016, 5:14 pm
- Has thanked: 2 times
- Been thanked: 132 times
Re: Excel formula please
Clitheroekid wrote:I've just wasted about half an hour trying to create a formula in Excel. I really have no idea what I'm doing, so I'm turning to my LF chums who can probably do it in a few seconds.
What I want the formula to do is to calculate a sum depending on the value in a given cell. In English I want it to say as follows:
If the value in cell A is between 0 and 999.99 then the result is 40; if it's between 1,000 and 9,999.99 then it's 70; and if it's 10,000 or more then the result is 100.
TIA for any assistance.
Assuming the value being tested is in A1 and the result is in B1 type the following into B1
=IF(A1>=10000,100,IF(A1>=1000,70,40))
-
- Lemon Quarter
- Posts: 1103
- Joined: November 4th, 2016, 1:12 pm
- Has thanked: 179 times
- Been thanked: 378 times
Re: Excel formula please
Clitheroekid wrote:I've just wasted about half an hour trying to create a formula in Excel. I really have no idea what I'm doing, so I'm turning to my LF chums who can probably do it in a few seconds.
What I want the formula to do is to calculate a sum depending on the value in a given cell. In English I want it to say as follows:
If the value in cell A is between 0 and 999.99 then the result is 40; if it's between 1,000 and 9,999.99 then it's 70; and if it's 10,000 or more then the result is 100.
TIA for any assistance.
=IF(a1<1000,40,IF(a1<10000,70,100))
should do it.
-
- Lemon Quarter
- Posts: 4137
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3292 times
- Been thanked: 2871 times
Re: Excel formula please
I think this will do it, though in a rush so haven't checked in detail
=IF(AND(A1>=0,A1<=999.9),40,IF(AND(A1>=1000,A1<=9999.99),70,100))
--kiloran
=IF(AND(A1>=0,A1<=999.9),40,IF(AND(A1>=1000,A1<=9999.99),70,100))
--kiloran
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Excel formula please
Does this abomination work?
=IF(AND(B4<10000,B4>=1000),70,IF(B4<1000,40,100))
=IF(AND(B4<10000,B4>=1000),70,IF(B4<1000,40,100))
-
- Lemon Slice
- Posts: 924
- Joined: February 5th, 2021, 4:45 pm
- Has thanked: 690 times
- Been thanked: 316 times
Re: Excel formula please
You need a nested if, e.g. https://www.techonthenet.com/excel/formulas/if_nested.php
Something like this (though it will fail if A is negative):
=IF(AND(A>=0,A<1000),40,IF(AND(A>=1000,A<10000), 70, 100))
Something like this (though it will fail if A is negative):
=IF(AND(A>=0,A<1000),40,IF(AND(A>=1000,A<10000), 70, 100))
-
- 2 Lemon pips
- Posts: 249
- Joined: November 14th, 2016, 5:14 pm
- Has thanked: 2 times
- Been thanked: 132 times
Re: Excel formula please
Kiloran's formula checked for non-negative values. You may also want to check the value is numeric and leave blank if not.
-
- Lemon Slice
- Posts: 924
- Joined: February 5th, 2021, 4:45 pm
- Has thanked: 690 times
- Been thanked: 316 times
Re: Excel formula please
kyu66 wrote:Kiloran's formula checked for non-negative values. You may also want to check the value is numeric and leave blank if not.
kilorans most precisely answered the OP's request, though is undefined for say 999.99, which may or may not be what the OP wanted.
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Excel formula please
I assumed it was
<1,000
>= 1,000 but less than 10,000
10,000 and above (>=10000)
<1,000
>= 1,000 but less than 10,000
10,000 and above (>=10000)
-
- Lemon Half
- Posts: 6050
- Joined: May 30th, 2021, 6:01 pm
- Has thanked: 1843 times
- Been thanked: 2068 times
-
- Lemon Slice
- Posts: 834
- Joined: November 4th, 2016, 6:29 pm
- Has thanked: 153 times
- Been thanked: 209 times
Re: Excel formula please
I guess using IFS would be simpler if using a version of Excel that supports it:
https://support.microsoft.com/en-us/off ... 39bd951d45
https://support.microsoft.com/en-us/off ... 39bd951d45
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Excel formula please
Is there a CASE/SWITCH function?
I really don't like nested IFs etc and tend to split stuff out into multiple columns and put some logic at the end
I really don't like nested IFs etc and tend to split stuff out into multiple columns and put some logic at the end
-
- Lemon Quarter
- Posts: 3499
- Joined: November 27th, 2016, 8:45 am
- Has thanked: 131 times
- Been thanked: 1278 times
Re: Excel formula please
An alternative is to use VLOOKUP with the formula set to TRUE.
For example -
=VLOOKUP(A1,A2:B5,2,TRUE)
With A1 being the value you want to assess,
Then in cells A2 to A5 the following - 0, 999.99 1000, then a very large number,
Then in cells B2 to B5 the following - 40, 70, 100, blank
By setting the formula to TRUE then Excel will try to match A1 in cells A2 to A5 but if it cannot find an exact match then it will go for the nearest lower number, and then return the contents of the cell in B2 to B5.
An advantage to doing this is that the values being assessed against and their results are visible and not 'hidden' in a formula and also can be easily changed if needed without amending individual formulas.
For example -
=VLOOKUP(A1,A2:B5,2,TRUE)
With A1 being the value you want to assess,
Then in cells A2 to A5 the following - 0, 999.99 1000, then a very large number,
Then in cells B2 to B5 the following - 40, 70, 100, blank
By setting the formula to TRUE then Excel will try to match A1 in cells A2 to A5 but if it cannot find an exact match then it will go for the nearest lower number, and then return the contents of the cell in B2 to B5.
An advantage to doing this is that the values being assessed against and their results are visible and not 'hidden' in a formula and also can be easily changed if needed without amending individual formulas.
-
- Lemon Slice
- Posts: 634
- Joined: March 22nd, 2017, 10:17 am
- Has thanked: 112 times
- Been thanked: 226 times
Re: Excel formula please
=IF(ISNUMBER(A1),
IF(A1<0,"Negative Number",
IF(A1<1000,40,
IF(A1<10000,70,
100))),
"Not a Number")
IF(A1<0,"Negative Number",
IF(A1<1000,40,
IF(A1<10000,70,
100))),
"Not a Number")
-
- Lemon Slice
- Posts: 834
- Joined: November 4th, 2016, 6:29 pm
- Has thanked: 153 times
- Been thanked: 209 times
Re: Excel formula please
AleisterCrowley wrote:Is there a CASE/SWITCH function?
It would seem so, though you need Excel 2019 or later:
https://exceljet.net/excel-functions/ex ... h-function
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Excel formula please
Ah, you can't use logical operators like <= 1000
Something like...
CASE
WHEN A1<1000 THEN 40
WHEN A1>=10000 THEN 100
ELSE 70
END
Something like...
CASE
WHEN A1<1000 THEN 40
WHEN A1>=10000 THEN 100
ELSE 70
END
-
- Lemon Half
- Posts: 8597
- Joined: November 8th, 2016, 5:56 am
- Has thanked: 4559 times
- Been thanked: 3681 times
Re: Excel formula please
Stompa wrote:AleisterCrowley wrote:Is there a CASE/SWITCH function?
It would seem so, though you need Excel 2019 or later:
https://exceljet.net/excel-functions/ex ... h-function
And wonderfully demonstrated in an example for which the VLOOKUP function is perfect
- and that predates Excel (or at least I remember it being in Lotus notes)
WARNING greybeard rant:
[rant]
I'm not a fan instances/languages/syntax that allow CASE conditions to be evaluated
- that page says this doesn't
- and then it says you can via the example
=SWITCH(TRUE,A1>=1000,"Gold",A1>=500,"Silver","Bronze")
at evaluation either, neither or both conditions could be true (or an error)
- but as SWITCH & CASE were intended to avoid that kind of thing it looks "safe", "clean" and "good" at a glance
- meanwhile it allows nyarlathotep levels of crawling chaos in to a spread sheet
you can have your gcc extensions to do things like CASE this ... that:
- but have your warnings on to pick up where you've left holes and treat those warnings as errors
OK? right?
[/rant]
- sd
-
- Lemon Slice
- Posts: 834
- Joined: November 4th, 2016, 6:29 pm
- Has thanked: 153 times
- Been thanked: 209 times
Re: Excel formula please
Of course you could always use:
=CHOOSE(MIN(INT(LOG10(A1))+1,5),40,40,40,70,100)
though it fails for A1=0.
For the avoidance of doubt, that's not a serious suggestion!
=CHOOSE(MIN(INT(LOG10(A1))+1,5),40,40,40,70,100)
though it fails for A1=0.
For the avoidance of doubt, that's not a serious suggestion!
-
- 2 Lemon pips
- Posts: 102
- Joined: October 24th, 2017, 3:29 pm
- Has thanked: 3 times
- Been thanked: 83 times
Re: Excel formula please
This is the canonical answer from Stompa. It is easier to read and avoids (i) ugly nested if statements (ii) cumbersome vlookups and (iii) some of the other entertaining abominations suggested above. But only on 2019+/Office 365. In my mind I think of it as the "if switch" statement
=IFS(A1>=10000,100,A1>=1000,70,A1>=0,40)
A negative number in the reference cell A1 will return #N/A as op did not provide guidance here
Stompa wrote:I guess using IFS would be simpler if using a version of Excel that supports it:
https://support.microsoft.com/en-us/off ... 39bd951d45
=IFS(A1>=10000,100,A1>=1000,70,A1>=0,40)
A negative number in the reference cell A1 will return #N/A as op did not provide guidance here
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Excel formula please
Data in B4, cos that's where I put the test column...
=IFS(B4>=10000,100,B4>=1000,70,B4<1000,40)
That works, but watch the order. (I'm too lazy to trap negative values)
=IFS(B4>=1000,70,B4>=10000,100,B4<1000,40)
Doesn't work - anything >=1,000 will return 70 and jump back from function, even values >=10,000
=IFS(B4>=10000,100,B4>=1000,70,B4<1000,40)
That works, but watch the order. (I'm too lazy to trap negative values)
=IFS(B4>=1000,70,B4>=10000,100,B4<1000,40)
Doesn't work - anything >=1,000 will return 70 and jump back from function, even values >=10,000
Return to “Technology - Computers, TV, Phones etc.”
Who is online
Users browsing this forum: No registered users and 12 guests