cancel
Showing results for 
Search instead for 
Did you mean: 

Advice on Excel formula, please!

Luzern
Seasoned Pro
Posts: 3,380
Thanks: 351
Fixes: 3
Registered: 31-07-2007

Advice on Excel formula, please!

Can someone advise me how to construct an Excel formula?
I wish to place in cell X1 a result subject to these conditions.
If Cell A1>n or=<n and not between clock times xx:xx:xx to yy:yy:yy, then result A, else result B. (n is a numerical value)
If it is easy I do not mind looking a numpty, but AND OR NOT functions, which I have a feeling will be invloved are not my forte.

Embarrassed Wink
No one has to agree with my opinion, but in the time I have left a miracle would be nice.
26 REPLIES
itsme
Grafter
Posts: 5,924
Thanks: 1
Registered: 07-04-2007

Re: Advice on Excel formula, please!

Have you used the Insert Function (Ctrl+F2)? This will take you through each stage to construct the formula.
Luzern
Seasoned Pro
Posts: 3,380
Thanks: 351
Fixes: 3
Registered: 31-07-2007

Re: Advice on Excel formula, please!

Control + F2 does nothing on ny computer (XP SP3 and Excel 2000).
No one has to agree with my opinion, but in the time I have left a miracle would be nice.
Community Veteran
Posts: 17,769
Thanks: 1,604
Fixes: 19
Registered: 06-11-2007

Re: Advice on Excel formula, please!

Thinking and typing at the same time.....! and trying to think "one step at a time"......
cell A1  is the result of  something in B2 which is >n
or
cell A1 is the result of  something in  B3 which is <n
B2 and B3 are the result of something in another cell.....  Your results can appear as positive or (negative) in A1.....
how you get the time factor into the equation I don`t know...... but maybe the above will help your brain think it out....!....
Superuser
Superuser
Posts: 6,775
Thanks: 856
Fixes: 55
Registered: 30-07-2007

Re: Advice on Excel formula, please!

I'd do it in two steps
Use another cell (say C1 ) for the time check as =IF(NOW()> TIME("xx:xx:xx") AND NOW()<TIME("yy:yy:yy");0;1)
Then X1 = IF((A1>n OR A1<n) AND C1;A;B)
Luzern
Seasoned Pro
Posts: 3,380
Thanks: 351
Fixes: 3
Registered: 31-07-2007

Re: Advice on Excel formula, please!

@MisterW
The time is that when something was recorded as having occurred . If that time laid between xx:xx:xx and yy:yy:yy, both as in hh:mm:ss, then the result in cell X1 is to be A, if conditions not met B.
I do not see how NOW function is applicable
No one has to agree with my opinion, but in the time I have left a miracle would be nice.
Luzern
Seasoned Pro
Posts: 3,380
Thanks: 351
Fixes: 3
Registered: 31-07-2007

Re: Advice on Excel formula, please!

@shutter
Cell A1 contains the WEEKDAY number obtained from a date (dd:mm:yy) in another cell in the same row. Let's say D.

If either A1's value is greater than n, the answer returned in X1 has to be A
If A1is equal or less than n and the time shown in another cell in the same row, say E1 is between xx:xx:xx to yy:yy:yy, then the answer returned in X1 has to be A
If neither criterion is met then the answer returned in X1 has to be B
No one has to agree with my opinion, but in the time I have left a miracle would be nice.
johpal
Grafter
Posts: 550
Registered: 20-04-2008

Re: Advice on Excel formula, please!

Example:
In cell A1 type a value
In cell B1 enter a time value [or use =NOW()]
In cell C1 enter the start value of the time range [eg 09:00:00]
In cell D1 enter the end value of the time range [eg 17:00:00]
Format cells B1, C1, D1 as Time [Format, Cells, Number, Time, (select the 00:00:00 option]
In the cell where you require your answer, type:
=IF(OR(A1>n,AND(B1<C1,B1>D1)),"A","B")
NB substitute a numerical value for "n"!
johpal
Grafter
Posts: 550
Registered: 20-04-2008

Re: Advice on Excel formula, please!

It took me 3 attempts to post that; first my login expired and lost the answer, then I hit a wrong key on my netbook (perched on my lap) and deleted the second attempt!
the IF function decides the true ("A") value, or the false ("B") value.
The argument of the IF function is firstly an OR function. If A1 > n, the argument is true; ignore the AND function. If  A1 <= n, check if another value falls outside "start time" AND "end time"; if it does, the argument is true, so IF is true. If the AND value is outside the range specified, the AND value is false; the IF argument is also false.
Note the syntax of the formula; the position and number of the parentheses is critical.
itsme
Grafter
Posts: 5,924
Thanks: 1
Registered: 07-04-2007

Re: Advice on Excel formula, please!

In Excel should the , be ;
In OpenOffice it is.
johpal
Grafter
Posts: 550
Registered: 20-04-2008

Re: Advice on Excel formula, please!

No experience with OpenOffice. The formula is exactly as it should be, copied and pasted from Excel.
The colon ( : ) is used as a separator in a range of cells given within a formula eg "=SUM(A1:A10)". Off hand, I can't think of a use of the semi-colon ( ; ) within a formula in Excel, but I won't state categorically that it doesn't have one. The keyboard shortcut, " Ctrl " and " ; " keys together, inserts the current date into the cell containing the cursor.
Community Veteran
Posts: 17,769
Thanks: 1,604
Fixes: 19
Registered: 06-11-2007

Re: Advice on Excel formula, please!

Yay!... it is a long time since I used Excel........ so I was just trying to get your mind into thinking about how the contents of A1 got there,......... by using the B2 and B3 cells as starting points, from which to work out the formula for changing the answer in A1... 
Sometimes it is easier to start with the answer (A1) (even if it is a totally unrelated number)... already in the cell, and then work backwards to how you get that answer to change depending in what is happening in other cells across the board... I think that the others are coming up with the "proper" formulae to help you in that, so I will now "take a back seat" and watch the fun !....
johpal
Grafter
Posts: 550
Registered: 20-04-2008

Re: Advice on Excel formula, please!

@shutter
Come on in... the water's lovely!
Give your brain some exercise. Replace the value "n" in the formula with the number 10; now enter the value 11 in cell A1. Answer = A. Now change the value to 9; answer  = B.
Without changing the value in cell A1, change the time in cell B1 to something within the hours of 9 - 5 [eg 12:00:00] Answer = A.
It's a damn sight better than the rubbish on ITV at the moment... Will nobody literally take the geezer out?
pierre_pierre
Grafter
Posts: 19,757
Registered: 30-07-2007

Re: Advice on Excel formula, please!

I'm watching the Darjeeling Railway story on BBC four
johpal
Grafter
Posts: 550
Registered: 20-04-2008

Re: Advice on Excel formula, please!

Number of persons in the room (n) = 4
Value of person in seat A1 =1
=IF(persons in room wanting to watch ITV > value of person in seat A1, "Watch ITV","Change channel")  Grin