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
- :
- Re: 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!
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!
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!
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!
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)
Superusers are not staff, but they do have a direct line of communication into the business in order to raise issues, concerns and feedback from the community.
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!
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!
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!
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!
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!
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!
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!
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!
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!
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
- :
- Re: Advice on Excel formula, please!