lookup functions

[Srinivas]

I want help on lookup functions i wrote up to 100000 but i want up to
1000000 how to do this using lookup or any other.

=LOOKUP(B224,{0,25000,50001,55001,60001,65001,70001,75001,80001,85001,90001,95001,100001},{"25","50","55","60","65","70","75","80","85","90","95","100"})

 

[Tom Hutchins]

See if this formula gives the intended results up to 1000000:

=IF(CEILING(B224/1000,5)<25,25,IF(CEILING(B224/1000,5)<50,50,CEILING(B224/1000,5)))

Hope this helps,

Hutch

 

[Srinivas]

Thanks for the reply

Actual the scenario is like this from 1 to 25000 - the bank will charge 25
as commision and from 25001 to 50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60 ..........etc i want formula up 100000. But if nothing
there like 0 it should display "0"

Thanks

 

[Srinivas]

Thank you it helped me but even if nothing is there it is giving 25, if no
amount is there it should display "0"

 

[Tom Hutchins]

Okay, here is revised version which will return 0 if B224 is empty or
contains a zero.

=IF(OR(LEN(B224)=0,B224=0),0,IF(CEILING(B224/1000,5)<25,25,IF(CEILING(B224/1000,5)<50,50,CEILING(B224/1000,5))))

Hutch

 

[Srinivas]

THANKS LOT
--
srinivas

Okay, here is revised version which will return 0 if B224 is empty or
contains a zero.

=IF(OR(LEN(B224)=0,B224=0),0,IF(CEILING(B224/1000,5)<25,25,IF(CEILING(B224/1000,5)<50,50,CEILING(B224/1000,5))))

Hutch