Excel - IF Function Problem - Expert Solution

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
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
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
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.