#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ridash
 
Posts: n/a
Default minimum


hi

how can i do the following equation?


40$ if the sum is less than 1000$
75$ if the sum is less than 1001$ t0 3000$
100$ if the sum is less than 3001$
3001$ and up no minimum

all should go in one cell

thanks
Rida


--
ridash
------------------------------------------------------------------------
ridash's Profile: http://www.excelforum.com/member.php...o&userid=35617
View this thread: http://www.excelforum.com/showthread...hreadid=554015

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default minimum

I'd probably do it with a lookup myself, but

=if(CellTested<1000,40,if(CellTested<3000,75,if(Ce llTested<3001,100,"no
minimum")))

will do it for you - I've written it with the bounds you specified

ridash wrote:
hi

how can i do the following equation?


40$ if the sum is less than 1000$
75$ if the sum is less than 1001$ t0 3000$
100$ if the sum is less than 3001$
3001$ and up no minimum

all should go in one cell

thanks
Rida


--
ridash
------------------------------------------------------------------------
ridash's Profile: http://www.excelforum.com/member.php...o&userid=35617
View this thread: http://www.excelforum.com/showthread...hreadid=554015


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dav
 
Posts: n/a
Default minimum


It can be done with anested if statement or with a vlookup. however your
example ranges overlap and so an answer can not be given your thrid
range <3000 is also covering the same date as your first 2 ranges

If you resate you will get an answer

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=554015

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default minimum

~Actually, the ranges don't overlap, but the last range is only a
dollar in size - hence my comment on my reply that I'd done it with the
ranges as specified!!

Dav wrote:
It can be done with anested if statement or with a vlookup. however your
example ranges overlap and so an answer can not be given your thrid
range <3000 is also covering the same date as your first 2 ranges

If you resate you will get an answer

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=554015


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dav
 
Posts: n/a
Default minimum


If you read my post I made no mention of the 4th range, however as the
third range does not have a lower bound, they do overlap

40$ if the sum is less than 1000$
75$ if the sum is less than 1001$ t0 3000$
100$ if the sum is less than 3001$
3001$ and up no minimum


in you logic 1000 is included in the less than 3000 statement, which
can not be true!

the 2nd statment makes no sense <1001 to 3000 yours would include 1000
as written

it is unclear also if < or <= should be used I would guess the later to
exclude gaps

Regards

Dav

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=554015

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
Second highest minimum in a group Jennifer Excel Worksheet Functions 1 March 6th 06 10:34 PM
minimum from various columns without zeroes joie Excel Worksheet Functions 5 November 29th 05 06:49 AM
second minimum value Ken Excel Discussion (Misc queries) 3 March 16th 05 07:17 PM
Hiding Values Below Z Axis Minimum Value on Axis Charts TryingToExcel Charts and Charting in Excel 1 February 18th 05 04:55 AM
Sum minimum values in a pivot table AK Excel Worksheet Functions 1 December 22nd 04 09:55 PM


All times are GMT +1. The time now is 10:37 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"