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
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
I'd probably do it with a lookup myself, but
=if(CellTested<1000,40,if(CellTested<3000,75,if(CellTested<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
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
~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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks