|
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. |
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. |
=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. |
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. |
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. |
| All times are GMT +1. The time now is 07:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com