Forum Discussion
Excel formula
Hello,
I am struggling in creating a calculator with the following information:
| VOLUME OF USERS | Professional | Corporate | Enterprise |
| User/month | User/month | User/month | |
| From 10 to 50 users | €6,00 | €7,00 | - |
| From 51 to 100 users | €5,00 | €6,00 | €8,00 |
| From 101 to 250 users | - | €5,00 | €6,00 |
| From 251 to 500 users | - | €4,50 | €5,00 |
| >500 | - | - | €4,20 |
I am trying to create a calculator where depending on the plan you select, and the number of users you add to this calculator, the € user/month appears on a cell.
I had advanced until this:
| Type of Plan | Users | Price month/user |
| "Drop down menu with the options" | "Here the user of the calculator makes the input of the number of user" | "Here is where I want the formula to give me me the right number according to the table shown before" |
The formula I have used is: =IF(AND(I9<51;G9="Professional");6;5) - But this only solves the problem for the Professional plan, but not the rest and when I try to add to the formula the rest of the conditions, it doesn´t work.
Could you please help me with this?
Kind regards,
2 Replies
Hi José,
To make the solution as easy as possible, you have to redesign the table as follows:
After that, you can use this formula in cell C12:
=VLOOKUP(B12,A3:E7,MATCH(A12,C1:E1,0)+2)
Please find the attached file.
Hope that helps
Haytham
Thank you so much! you are the men :)
