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.
