## Advice on Excel formula, please!

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Plusnet Community
- :
- Forum
- :
- Other forums
- :
- Tech Help - Software/Hardware etc
- :
- Advice on Excel formula, please!

##
##### Advice on Excel formula, please!

##### Advice on Excel formula, please!

20-02-2010 12:58 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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.

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 1:35 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 4:14 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 5:45 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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....!....

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 5:57 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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)

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 6:53 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 7:08 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 7:21 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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"!

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 7:32 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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.

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 7:46 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

In OpenOffice it is.

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 8:05 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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.

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 8:15 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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 !....

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 8:27 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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?

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 8:32 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

##
##### Re: Advice on Excel formula, please!

##### Re: Advice on Excel formula, please!

20-02-2010 8:42 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator

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

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Plusnet Community
- :
- Forum
- :
- Other forums
- :
- Tech Help - Software/Hardware etc
- :
- Advice on Excel formula, please!