IF statements with more than 7 variables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Liv4fun - 17 Nov 2005 23:42 GMT

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!

Signature

Liv4fun

Reply to this Message

tjtjjtjt - 18 Nov 2005 00:15 GMT

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

Signature

> I am trying to create a proposal form that based on the number of users
> the form changes prices.
[quoted text clipped - 7 lines]
>
> I hope this makes sense!

Reply to this Message

Elkar - 18 Nov 2005 00:23 GMT

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

> I am trying to create a proposal form that based on the number of users
> the form changes prices.
[quoted text clipped - 7 lines]
>
> I hope this makes sense!

Reply to this Message

Bruno Campanini - 18 Nov 2005 00:44 GMT

> I am trying to create a proposal form that based on the number of users
> the form changes prices.
[quoted text clipped - 7 lines]
>
> 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

Reply to this Message

Liv4fun - 18 Nov 2005 03:03 GMT

Bruno Campanini Wrote:

> > I am trying to create a proposal form that based on the number of
> users
[quoted text clipped - 36 lines]
>
> 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.

Signature

Liv4fun

Reply to this Message

JMB - 18 Nov 2005 04:25 GMT

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?

> Bruno Campanini Wrote:
> > >
[quoted text clipped - 43 lines]
> users say 15 would be the 10 user price 5 times plus the other 9
> prices.

Reply to this Message

JMB - 18 Nov 2005 04:28 GMT

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

> Bruno Campanini Wrote:
> > >
[quoted text clipped - 43 lines]
> users say 15 would be the 10 user price 5 times plus the other 9
> prices.

Reply to this Message

Robert_Steel@nothanks.com - 18 Nov 2005 14:30 GMT

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

Reply to this Message

SteveG - 18 Nov 2005 15:26 GMT

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

Signature

SteveG

Reply to this Message