#1   Report Post  
SM
 
Posts: n/a
Default 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   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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   Report Post  
Bill Kuunders
 
Posts: n/a
Default

=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   Report Post  
SM
 
Posts: n/a
Default

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   Report Post  
Bill Kuunders
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM
Need a ISWorkday Function -- Any Ideas Mark Excel Worksheet Functions 5 March 29th 05 01:58 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM


All times are GMT +1. The time now is 06:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"