Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements with more than 7 variables
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements with more than 7 variables
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements with more than 7 variables
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements with more than 7 variables
"Liv4fun" wrote in
message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements with more than 7 variables
Bruno Campanini Wrote: "Liv4fun" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements with more than 7 variables
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,FALS E)) 15 users would be the 10 user price 5 times plus the other 9 prices - or 10 prices? "Liv4fun" wrote: Bruno Campanini Wrote: "Liv4fun" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements with more than 7 variables
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,FAL SE)) "Liv4fun" wrote: Bruno Campanini Wrote: "Liv4fun" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements with more than 7 variables
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements with more than 7 variables
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(A20,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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=486092 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Variables | Excel Discussion (Misc queries) | |||
How do I use variables in Excel? | Excel Discussion (Misc queries) | |||
Linking two IF statements together | Excel Discussion (Misc queries) | |||
Plot 2 variables on Y axis | Charts and Charting in Excel | |||
Nested IF statements | Excel Worksheet Functions |