if statement

[nuprules]

I have a question. here is what i have so far. I want to know if the change
for a specific account meets or doesnt meet the criteria. for example for the
first data i want it to lookup the account number 7400852 in the table and
then see if the change falls in between those values. if it does than it does
not meet the criteria and if it doesn't then it meets criteria. Anyone help
me out?

Table

Account# Description % Decrease $ Increase $
7400700 Housekeeping 25% ($1,000) $1,000
7400852 Disposal 25% ($500) $500
7400853 Inf waste 25% ($800) $1,000
7400854 Cleaning 25% ($1,000) $2,000
7400856 Security 25% ($1,000) $1,000



Data
account change meet criteria?
7400852 1200
7400856 300

 

[Sandy Mann]

With the table that you supplied in A1:E6 and the Data in A11:C13 then try
the formula:

=IF(MEDIAN(VLOOKUP(A12,$A$1:$E$6,4,0),VLOOKUP(A12,$A$1:$E$6,5,0),B12)=B12,"Does
not meet criteria","Meets criteria")

will tell you is the number in the Data is between the two numbers in the
table or not.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[FXone]

maybe you can help solve this one..

=IF(TODAY()=C3,0,NOW()-C3-1)

the days open are counted based on a start date manually inputed in C3, a closed date is manually inputed in a separate cell, for instance C4. I am trying to craft my IF statement to stop counting days open based on the closure date. Any suggestions? Thx

for example:

DtOpen Days Open DtClosed
05/1/08 26
05/1/08 24 05/25/08

 

[Sandy Mann]

If I understand you correctly, with the Start date in C3, and a Closed date
in E3 then in D2 enter the formula:

=IF(TODAY()<C3,"",IF(AND(E3<>"",E3<C3),"Error in
dates!",IF(E3="",TODAY()-C3,MIN(TODAY(),E3)-C3)))

This will display the number of days since the start date, wherther of not
there is a Closed date entered, but will stop counting when the Closed date
is reached. That is even if there is a Closed date entered, it will still
only show the number of days since the Start date until the Closed date is
reached.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk