I am trying to create a proposal form that based on the number of users the form changes prices.
I have a cell for number of users and then have a list of prices based upon the number of users. So if they say 4 users it takes the base price and ads the price for the 4 users. My problem is I can only say "IF" 7 times thus I can only price 1-7 users and I need to get to a point where it is 10 or more users. Is there another way other than using the IF statement?
I hope this makes sense!
In short, no.
You should be able to get a solution with VLOOKUP, or another lookup
function. If you post more specific information about you sheet layout,
someone here can help you.
Or, you can search this group for VLOOKUP. Or, you can try some websites.
Here is one to get you started:
http://www.contextures.com/xlFunctions02.html
--
tj
"Liv4fun" wrote:
>
> I am trying to create a proposal form that based on the number of users
> the form changes prices.
>
> I have a cell for number of users and then have a list of prices based
> upon the number of users. So if they say 4 users it takes the base
> price and ads the price for the 4 users. My problem is I can only say
> "IF" 7 times thus I can only price 1-7 users and I need to get to a
> point where it is 10 or more users. Is there another way other than
> using the IF statement?
>
> I hope this makes sense!
>
>
> --
> Liv4fun
> ------------------------------------------------------------------------
> Liv4fun's Profile: http://www.excelforum.com/member.php...o&userid=28858
> View this thread: http://www.excelforum.com/showthread...hreadid=486092
>
>
Here's what I would suggest:
1. Create a new blank worksheet (we'll call it "Sheet2")
2. Fill in Column A with the Numbers of Users (1, 2, 3, 4, 5, etc...)
3. Fill in Column B with the price associated with each User Number in
Column A
4. Go back to your original worksheet
5. In the cell you want your formula in, enter:
=VLOOKUP(A1,Sheet2!A:B,2,0)+B1
The above formula assumes that the number of users is stored in cell A1 and
your Base Price is in cell B1. You can adjust this accordingly. This will
allow you to have as many Users as you want and make it easy to adjust prices
in the future without having to edit your formula.
HTH,
Elkar
"Liv4fun" wrote:
>
> I am trying to create a proposal form that based on the number of users
> the form changes prices.
>
> I have a cell for number of users and then have a list of prices based
> upon the number of users. So if they say 4 users it takes the base
> price and ads the price for the 4 users. My problem is I can only say
> "IF" 7 times thus I can only price 1-7 users and I need to get to a
> point where it is 10 or more users. Is there another way other than
> using the IF statement?
>
> I hope this makes sense!
>
>
> --
> Liv4fun
> ------------------------------------------------------------------------
> Liv4fun's Profile: http://www.excelforum.com/member.php...o&userid=28858
> View this thread: http://www.excelforum.com/showthread...hreadid=486092
>
>
"Liv4fun" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to create a proposal form that based on the number of users
> the form changes prices.
>
> I have a cell for number of users and then have a list of prices based
> upon the number of users. So if they say 4 users it takes the base
> price and ads the price for the 4 users. My problem is I can only say
> "IF" 7 times thus I can only price 1-7 users and I need to get to a
> point where it is 10 or more users. Is there another way other than
> using the IF statement?
>
> I hope this makes sense!
Let me put an example to see if I have got ok what you need
Given this table
Base price = 80
Users = 3
Price for Users
150 1
145 2
140 3
135 4
130 5
125 6
120 7
115 8
110 9
105 10
you want a formula calculating 220,
if Users = 8 then result = 195
if Users = 10 then result = 185 and so on.
???
Bruno
Yes pretty close. Actually if it were 8 users it would be the base price plus user 1 plus user 2 plus user 3 and so on. Anything over 10 users say 15 would be the 10 user price 5 times plus the other 9 prices.Originally Posted by Bruno Campanini
Assuming the base price is in cell B1, cell E1 has the number of users you
want, your table is on Sheet1 (cells A1:B10 - column A has number of users,
column B has price), one possibility:
=B1+SUMPRODUCT(--(Sheet1!A1:A10<=E1),(Sheet1!B1:B10))+((E1-MAX(Sheet1!A1:A10)-1)*VLOOKUP(MAX(Sheet1!A1:A10),Sheet1!A1:B10,2,FALSE))
15 users would be the 10 user price 5 times plus the other 9 prices - or 10
prices?
"Liv4fun" wrote:
>
> Bruno Campanini Wrote:
> > "Liv4fun" <[email protected]> wrote
> > in
> > message news:[email protected]...
> > >
> > > I am trying to create a proposal form that based on the number of
> > users
> > > the form changes prices.
> > >
> > > I have a cell for number of users and then have a list of prices
> > based
> > > upon the number of users. So if they say 4 users it takes the base
> > > price and ads the price for the 4 users. My problem is I can only
> > say
> > > "IF" 7 times thus I can only price 1-7 users and I need to get to a
> > > point where it is 10 or more users. Is there another way other than
> > > using the IF statement?
> > >
> > > I hope this makes sense!
> >
> > Let me put an example to see if I have got ok what you need
> > Given this table
> >
> > Base price = 80
> > Users = 3
> >
> > Price for Users
> > 150 1
> > 145 2
> > 140 3
> > 135 4
> > 130 5
> > 125 6
> > 120 7
> > 115 8
> > 110 9
> > 105 10
> >
> >
> >
> > you want a formula calculating 220,
> > if Users = 8 then result = 195
> > if Users = 10 then result = 185 and so on.
> >
> > ???
> >
> > Bruno
>
> Yes pretty close. Actually if it were 8 users it would be the base
> price plus user 1 plus user 2 plus user 3 and so on. Anything over 10
> users say 15 would be the 10 user price 5 times plus the other 9
> prices.
>
>
> --
> Liv4fun
> ------------------------------------------------------------------------
> Liv4fun's Profile: http://www.excelforum.com/member.php...o&userid=28858
> View this thread: http://www.excelforum.com/showthread...hreadid=486092
>
>
Correction:
B1+SUMPRODUCT(--(Sheet1!A1:A10<=E1),(Sheet1!B1:B10))+((MAX(0,E1-MAX(Sheet1!A1:A10)-1))*VLOOKUP(MAX(Sheet1!A1:A10),Sheet1!A1:B10,2,FALSE))
"Liv4fun" wrote:
>
> Bruno Campanini Wrote:
> > "Liv4fun" <[email protected]> wrote
> > in
> > message news:[email protected]...
> > >
> > > I am trying to create a proposal form that based on the number of
> > users
> > > the form changes prices.
> > >
> > > I have a cell for number of users and then have a list of prices
> > based
> > > upon the number of users. So if they say 4 users it takes the base
> > > price and ads the price for the 4 users. My problem is I can only
> > say
> > > "IF" 7 times thus I can only price 1-7 users and I need to get to a
> > > point where it is 10 or more users. Is there another way other than
> > > using the IF statement?
> > >
> > > I hope this makes sense!
> >
> > Let me put an example to see if I have got ok what you need
> > Given this table
> >
> > Base price = 80
> > Users = 3
> >
> > Price for Users
> > 150 1
> > 145 2
> > 140 3
> > 135 4
> > 130 5
> > 125 6
> > 120 7
> > 115 8
> > 110 9
> > 105 10
> >
> >
> >
> > you want a formula calculating 220,
> > if Users = 8 then result = 195
> > if Users = 10 then result = 185 and so on.
> >
> > ???
> >
> > Bruno
>
> Yes pretty close. Actually if it were 8 users it would be the base
> price plus user 1 plus user 2 plus user 3 and so on. Anything over 10
> users say 15 would be the 10 user price 5 times plus the other 9
> prices.
>
>
> --
> Liv4fun
> ------------------------------------------------------------------------
> Liv4fun's Profile: http://www.excelforum.com/member.php...o&userid=28858
> View this thread: http://www.excelforum.com/showthread...hreadid=486092
>
>
If your costs per extra user always decrease by the same amount you can
create a single formula that does not rely on a look up table
harking back to school days and with a little bit of help from Dr Maths
http://mathforum.org/dr.math/
1+2+3+4+.....+n
=n((n+1)/2)
http://mathforum.org/library/drmath/view/56073.html
for the proof
It the case of
150 + 145 + 140 +...+ (150-5*(n-1))
=n(150+(150-5*(n-1)))/2
or generaly
=Users(First+(First-Step*(Users-1)))/2
You can simplify this down. I have lest it expanded to make it easier to
addapt.
On a lighter note - if the rule stands - more than 61 users and you will
pay them to take it.
******************************
Alternatively
If you would prefer to use a lookup type table I would use the Offset
function
It is a Volatile function so could affect calculate speed. But good if
this is not an issue.
List of prices in A1:A11 including a header
B1 location of number of users
=SUM(OFFSET(A1:A11,1,0,B1,1))
******************************
hth RES
It's kind of long but you could do this without a lookup. If in R1 your column headers read something like:
A: Number of Users
B: Base Price
C: Proposal Price
D: Price per user from 1-10 ascending.
In column C type the formula:
=IF(A2>0,CHOOSE(A2,SUM(D2,B2),SUM(D3,B2),SUM(D4,B2),SUM(D5,B2),SUM(D6,B2),SUM(D7,B2),SUM(D8,B2),SUM(D9,B2),SUM(D10,B2),SUM(D11,B2)),B2)
I made the Proposal Price stay at the base if cell A2 is blank or = 0.
Regards,
Steve
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks