Formula to check when a number crosses threshold

mozza90

New Member
Joined
Mar 19, 2015
Messages
32
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:


ProductYesterdayTodayPrompt
A19.1%20.5%CHECK
B33%38%OK
C28%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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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%?
 
Upvote 0
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"
 
Upvote 0
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"))
 
Upvote 0
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")
 
Upvote 0
Thank you all, all 3 solutions work and are far simpler than what I had in place. Merry Christmas :)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top