**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need to create a formula for pricing a software license. The price varies depending on the number of users. If the users are less than 201, the price is the number of users divided by 50 times $200, if the users are 201+ but less than 301, the price is $1000, if the users are 301+ but less than 401, the price is $1200, if the users are 401+ the price is $1400.

Solved by S. D. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
04/04/2018 - 04:25

Hi

Excelchat Expert
04/04/2018 - 04:25

Welcome to got it Pro

Excelchat Expert
04/04/2018 - 04:29

hey please refer to column A and B

Excelchat Expert
04/04/2018 - 04:29

i have made formula as per the conditions mentioned by you

Excelchat Expert
04/04/2018 - 04:30

=IF(A11<201,A11*200/50,IF(AND(A11>2=201,A11<301),1000,IF(AND(A11>2=301,A11<401),1200,1400)))

User
04/04/2018 - 04:30

does it work for above 500 students

Excelchat Expert
04/04/2018 - 04:30

let me see

Excelchat Expert
04/04/2018 - 04:32

see now

User
04/04/2018 - 04:32

the a column would need to be a number above 500

Excelchat Expert
04/04/2018 - 04:33

see it is working

Excelchat Expert
04/04/2018 - 04:33

where do you see it not working?

User
04/04/2018 - 04:33

Also in the IF(AND(A11>2 doesn't make sense it should be 201 not 2??

Excelchat Expert
04/04/2018 - 04:34

where?

User
04/04/2018 - 04:34

IF(AND(A11>2=201,A11<301) - see there is a >2

Excelchat Expert
04/04/2018 - 04:34

i have changed the fomula already

Excelchat Expert
04/04/2018 - 04:34

please see

Excelchat Expert
04/04/2018 - 04:34

where is it now?

Excelchat Expert
04/04/2018 - 04:34

= was misplaced

Excelchat Expert
04/04/2018 - 04:35

it is working absolutely fine

Excelchat Expert
04/04/2018 - 04:35

let me know if you face issues

User
04/04/2018 - 04:35

ok. Also at the end of the formula why is there a " " please explain

Excelchat Expert
04/04/2018 - 04:35

it means if the values are not in any condition then ""

Excelchat Expert
04/04/2018 - 04:35

which means blank value

User
04/04/2018 - 04:35

Ah, thank you. How would i reach out for further help from this sit

Excelchat Expert
04/04/2018 - 04:35

as long as numbers are entered there we should not face any issue

Excelchat Expert
04/04/2018 - 04:36

great

Excelchat Expert
04/04/2018 - 04:36

please do give good rating

Excelchat Expert
04/04/2018 - 04:36

it really helps

User
04/04/2018 - 04:36

Is there a charge for the next query

Excelchat Expert
04/04/2018 - 04:36

i dont know..you will need to ask that from got it team

User
04/04/2018 - 04:36

OK thank you.

Excelchat Expert
04/04/2018 - 04:37

welcome

Excelchat Expert
04/04/2018 - 04:37

please do give good rating..it really helps

Excelchat Expert
04/04/2018 - 04:38

you can end the session now i guess

User
04/04/2018 - 04:38

Wait one more question on this formula please

User
04/04/2018 - 04:40

If the value in Column A (number of users) is less than 201, we divide the number of users by 50 and multiply by $200 - however we really only want to have pricing in multiples of round numbers. So pricing for 0-50 is 200, 51-100 is 400, 101-150 is 600, 151-200 is 800. So how do we write the first part of the formula to only return those values - not for example 792.

Excelchat Expert
04/04/2018 - 04:40

ok

Excelchat Expert
04/04/2018 - 04:44

=IF(A2<=50,200,IF(AND(A2>=51,A2<=100),400,IF(AND(A2>=101,A2<=150),600,IF(AND(A2>=151,A2<=200),800))))

Excelchat Expert
04/04/2018 - 04:44

use this in initial part

Excelchat Expert
04/04/2018 - 04:44

see D2

Excelchat Expert
04/04/2018 - 04:44

it would work till 200

User
04/04/2018 - 04:45

Is it possible for you to paste both sections together so that I don't mess up the last part

Excelchat Expert
04/04/2018 - 04:45

ok

Excelchat Expert
04/04/2018 - 04:46

=IF(A2<=50,200,IF(AND(A2>=51,A2<=100),400,IF(AND(A2>=101,A2<=150),600,IF(AND(A2>=151,A2<=200),800,IF(AND(A2>=201,A2<301),1000,IF(AND(A2>=301,A2<401),1200,IF(A2>401,1400,"")))))))

Excelchat Expert
04/04/2018 - 04:46

see E2

Excelchat Expert
04/04/2018 - 04:46

hope this helps now

User
04/04/2018 - 04:47

Thank you very much. How would I protect the cell when sharing this with others so that they don't inadvertently change the formula

Excelchat Expert
04/04/2018 - 04:47

https://www.laptopmag.com/articles/lock-cells-excel

Excelchat Expert
04/04/2018 - 04:47

refer to this

User
04/04/2018 - 04:48

thank you very much you rock

Excelchat Expert
04/04/2018 - 04:48

welcome

User
04/04/2018 - 04:48

How do i save this session

Excelchat Expert
04/04/2018 - 04:49

click on file on top left and save this

Excelchat Expert
04/04/2018 - 04:49

download as excel

User
04/04/2018 - 04:49

Wait I clicked on the file and now its gone what???

User
04/04/2018 - 04:50

Nothing there ...

Excelchat Expert
04/04/2018 - 04:50

refresh it

User
04/04/2018 - 04:50

Just says document preview

User
04/04/2018 - 04:50

are you sure

Excelchat Expert
04/04/2018 - 04:50

click on file and download it

Excelchat Expert
04/04/2018 - 04:50

thats it

User
04/04/2018 - 04:50

there is no longer any file

Excelchat Expert
04/04/2018 - 04:50

=IF(A2<=50,200,IF(AND(A2>=51,A2<=100),400,IF(AND(A2>=101,A2<=150),600,IF(AND(A2>=151,A2<=200),800,IF(AND(A2>=201,A2<301),1000,IF(AND(A2>=301,A2<401),1200,IF(A2>401,1400,"")))))))

Excelchat Expert
04/04/2018 - 04:50

copy this formula

User
04/04/2018 - 04:53

Ok got it thank you very much

Excelchat Expert
04/04/2018 - 04:54

welcome

Excelchat Expert
04/04/2018 - 04:54

please do give good rating

Excelchat Expert
04/04/2018 - 04:54

it would really help

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*