The Lemon Fool

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.

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

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.

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

Does this abomination work?
=IF(AND(B4<10000,B4>=1000),70,IF(B4<1000,40,100))

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

Kiloran's formula checked for non-negative values. You may also want to check the value is numeric and leave blank if not.

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.

I assumed it was
<1,000
>= 1,000 but less than 10,000
10,000 and above (>=10000)

Good to know there some are Excel wizards on here
I use it everyday

I guess using IFS would be simpler if using a version of Excel that supports it:

https://support.microsoft.com/en-us/off ... 39bd951d45

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

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.

=IF(ISNUMBER(A1),
IF(A1<0,"Negative Number",
IF(A1<1000,40,
IF(A1<10000,70,
100))),
"Not a Number")

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

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

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

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!

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

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

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