Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF function
I'm having bother with the IF function.
Cell A1 contains a figure derived from another worksheet. Cell A2 contains 4895 Cell A3 is A1-A2 Cell A4 contains 2090 Cell A5 is A3-A4 Cell B3 is 10% but I'd prefer it to be referred to as B3 as this figure may change If A3 is less than 2090 (A4) I want C3 to be A3 x B3 If A3 is a minus figure I want C3 to be zero. If A3 is greater than 2090 (A4) then I want C3 to be A4 x B3 This is the simplified version of it. Any attempts at solving this on the real worksheet result in circular references. Any help would be appreciated as a big fat computer monitor is about to find itself being thrown through the window of a first floor flat onto the street below. |
#2
|
|||
|
|||
One way:
C3: =MIN(MAX(0,A3),A4)*B3 In article , "SM" wrote: I'm having bother with the IF function. Cell A1 contains a figure derived from another worksheet. Cell A2 contains 4895 Cell A3 is A1-A2 Cell A4 contains 2090 Cell A5 is A3-A4 Cell B3 is 10% but I'd prefer it to be referred to as B3 as this figure may change If A3 is less than 2090 (A4) I want C3 to be A3 x B3 If A3 is a minus figure I want C3 to be zero. If A3 is greater than 2090 (A4) then I want C3 to be A4 x B3 This is the simplified version of it. Any attempts at solving this on the real worksheet result in circular references. Any help would be appreciated as a big fat computer monitor is about to find itself being thrown through the window of a first floor flat onto the street below. |
#3
|
|||
|
|||
=IF(A3<0,0,IF(A3<A4,A3*B3,A4*B3))
should work -- Greetings from New Zealand Bill K "SM" wrote in message .. . I'm having bother with the IF function. Cell A1 contains a figure derived from another worksheet. Cell A2 contains 4895 Cell A3 is A1-A2 Cell A4 contains 2090 Cell A5 is A3-A4 Cell B3 is 10% but I'd prefer it to be referred to as B3 as this figure may change If A3 is less than 2090 (A4) I want C3 to be A3 x B3 If A3 is a minus figure I want C3 to be zero. If A3 is greater than 2090 (A4) then I want C3 to be A4 x B3 This is the simplified version of it. Any attempts at solving this on the real worksheet result in circular references. Any help would be appreciated as a big fat computer monitor is about to find itself being thrown through the window of a first floor flat onto the street below. |
#4
|
|||
|
|||
That worked perfectly - you've just saved the car below my window from
having a computer monitor land on its roof. "Bill Kuunders" wrote in message ... =IF(A3<0,0,IF(A3<A4,A3*B3,A4*B3)) should work -- Greetings from New Zealand Bill K "SM" wrote in message .. . I'm having bother with the IF function. Cell A1 contains a figure derived from another worksheet. Cell A2 contains 4895 Cell A3 is A1-A2 Cell A4 contains 2090 Cell A5 is A3-A4 Cell B3 is 10% but I'd prefer it to be referred to as B3 as this figure may change If A3 is less than 2090 (A4) I want C3 to be A3 x B3 If A3 is a minus figure I want C3 to be zero. If A3 is greater than 2090 (A4) then I want C3 to be A4 x B3 This is the simplified version of it. Any attempts at solving this on the real worksheet result in circular references. Any help would be appreciated as a big fat computer monitor is about to find itself being thrown through the window of a first floor flat onto the street below. |
#5
|
|||
|
|||
Thanks for the feed back
I must say that J.E.Mc's solution was very interesting. Greetings from New Zealand Bill K "SM" wrote in message .. . That worked perfectly - you've just saved the car below my window from having a computer monitor land on its roof. "Bill Kuunders" wrote in message ... =IF(A3<0,0,IF(A3<A4,A3*B3,A4*B3)) should work -- Greetings from New Zealand Bill K "SM" wrote in message .. . I'm having bother with the IF function. Cell A1 contains a figure derived from another worksheet. Cell A2 contains 4895 Cell A3 is A1-A2 Cell A4 contains 2090 Cell A5 is A3-A4 Cell B3 is 10% but I'd prefer it to be referred to as B3 as this figure may change If A3 is less than 2090 (A4) I want C3 to be A3 x B3 If A3 is a minus figure I want C3 to be zero. If A3 is greater than 2090 (A4) then I want C3 to be A4 x B3 This is the simplified version of it. Any attempts at solving this on the real worksheet result in circular references. Any help would be appreciated as a big fat computer monitor is about to find itself being thrown through the window of a first floor flat onto the street below. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |