Formula to check when a number crosses threshold

mozza90

New Member
Hi there,

I have a set of data which changes daily. Column B = Yesterday's values in %, Column C = today's values in %. If the number in column C crosses a threshold then I would like a prompt of some sort to say so. Initial thoughts are that this could be an IF statement which returns "Check" for example.

The thresholds are 20%, 30%, 40% and 50% and should trigger when today's value (column B) goes over or under this value. For example, yesterday was 19.1% and today it is 20.5% would return a prompt notifying the user. However if it goes from 33% to 38% then no prompt is required as it has remained within the threshold. For example:

 Product Yesterday Today Prompt A 19.1% 20.5% CHECK B 33% 38% OK C 28% 19.9% CHECK

<tbody>
</tbody>

At the moment I have the beginnings of a very long IF statement but there must be a simpler/more concise way to do this? Right?

Thanks!

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

AliGW

Banned
Do we need to take yesterday's value into account? For instance, with 38%, how will Excel know that it's OK and not a dip below 40%?

mozza90

New Member
Yes exactly, so it's saying; yesterday the value was 33% and today is 38%, that hasn't crossed a threshold so no prompt is required. Whereas the third line is saying: yesterday I was 28% and today I am 19.9%, I have crossed a threshold so prompt the user.

At the moment I have an IF statement looking up a table like the below:

20% 30%
30% 40%
40% 50%

which says if yesterday's value is between 20% & 30%, and today's value is greater than 30% or less than 20% then "CHECK", if still between the threshold - "OK"

Tetra201

MrExcel MVP
See if this workd for you (note: lightly tested):

=IF(INT(B2*10)=INT(C2*10),"OK",IF(OR(MAX(B2:C2)<0.2,MIN(B2:C2)>0.5),"OK","CHECK"))

AliGW

Banned
Maybe this:

Excel 2016 (Windows) 32 bit
ABCDEFG
1ProductYesterdayTodayPrompt10%1
2A19.10%20.50%CHECKCHECK20%2
3B33%38%OKOK30%3
4C28%19.90%CHECKCHECK40%4
550%5
Sheet5
Cell Formulas
RangeFormula
E2=IF(VLOOKUP(B2,\$F\$1:\$G\$5,2)=VLOOKUP(C2,\$F\$1:\$G\$5,2),"OK","CHECK")

sunny102

Board Regular
Use this formula, in column D

=IF(ROUNDDOWN(B2,1)<>ROUNDDOWN(C2,1),"CHECK","OK")

mozza90

New Member
Thank you all, all 3 solutions work and are far simpler than what I had in place. Merry Christmas

AliGW

Banned
Glad to have helped! Merry Christmas.

Replies
2
Views
135
Replies
5
Views
627
Replies
2
Views
503
Replies
0
Views
416
Replies
10
Views
300

1,195,593
Messages
6,010,624
Members
441,558
Latest member
lambierules

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back