# Complicated if and then function

Solved/Closed
Sunny Adam - Oct 18, 2010 at 07:18 AM
Ambucias Posts 47357 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 - Oct 21, 2010 at 05:08 PM
Hello,

I work in a firm where we get paid for our work in different rates. We enter data in forms. the different rates are as follows: If less than 100 forms - 3.25/form If more than 100 but less than 120 - 3.50/form (for all forms) If more than 120 forms - 3.75/form (only the forms entered after 120 the previous forms before 120 will be charged as 3.50/form) If more than 160 forms - 4.50/form (only the forms entered after 160 will be charged as 4.50/form) its too complicated i will deeply appreciate if some tech-guru can solve this problem for us. Thanks in advance.

Related:

## 2 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 18, 2010 at 09:26 PM
try this formula

=IF(A8<100,A8*3.25,IF(AND(A8>=100,A8<120),A8*3.5,IF(AND(A8>=120,A8<160),120*3.5+(A8-120)*3.75,IF(A8>=160,120*3.5+40*3.75+(A8-160)*4.5))))

assumption the no. of forms is in A8

check a few examples manally.

you can also use select case
brilliant!!!!!!! you are a genius!! it will help me thanks a lot
but still the result is not accurate enough perhaps i mistook the rates/form.. my bad..
Thanks again!!
Ambucias Posts 47357 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,179
Oct 21, 2010 at 05:08 PM
Isn't he wonderful? Venkat is one of our most illustreous contributors. We don't see him often, but when he makes an appearance, everybody notices and is overwhelmed in aw