IF statements with more than 7 variables

[Liv4fun]

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!

 

[Guest]

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

 

[Guest]

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

 

[Bruno Campanini]

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

 

[Liv4fun]

in
message news:[email protected]...

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.

 

[Guest]

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?

 

[Guest]

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

 

[Robert_Steel]

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

 

[SteveG]

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