Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IFs and multiples
Hi,
I work for a publishing company and Im designing a spreadsheet for our clients. The idea is that the client specifies a few variables and the spreadsheet will tell them how much the project will cost and how long it will take. One of things I want to do is have them enter the word count of the article they want written. In another cell, I want to put a formula that will check the word count and calculate the following using nested IF statements (assuming thats the best way to do it): ¢ If the word count is zero then populate this cell with the value 0 ¢ If the word count is more than zero, for the first 500 words populate the cell with the number 3, for every subsequent 500 words add the number 1.5 to the initial 3. So if the wordcount 1000 is entered into A1 and the formula is in B1, the value in B1 would be 4.5. The value in B1 represents the number of hours it will take to write the article. Is this possible? If it is, can anyone help me with the formula? Many thanks Karl |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IFs and multiples
Hi Karl
This formula should do it: =IF(A1=0,0,IF(A1<=500,3,ROUNDUP((A1-500)/500,0)*1.5+3)) Best regards, Per "Karl" skrev i meddelelsen ... Hi, I work for a publishing company and Im designing a spreadsheet for our clients. The idea is that the client specifies a few variables and the spreadsheet will tell them how much the project will cost and how long it will take. One of things I want to do is have them enter the word count of the article they want written. In another cell, I want to put a formula that will check the word count and calculate the following using nested IF statements (assuming thats the best way to do it): ¢ If the word count is zero then populate this cell with the value 0 ¢ If the word count is more than zero, for the first 500 words populate the cell with the number 3, for every subsequent 500 words add the number 1.5 to the initial 3. So if the wordcount 1000 is entered into A1 and the formula is in B1, the value in B1 would be 4.5. The value in B1 represents the number of hours it will take to write the article. Is this possible? If it is, can anyone help me with the formula? Many thanks Karl |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IFs and multiples
=IF(A1=500,3,0)+(INT(A1/500)-1)*1.5
If this post helps click Yes --------------- Jacob Skaria "Karl" wrote: Hi, I work for a publishing company and Im designing a spreadsheet for our clients. The idea is that the client specifies a few variables and the spreadsheet will tell them how much the project will cost and how long it will take. One of things I want to do is have them enter the word count of the article they want written. In another cell, I want to put a formula that will check the word count and calculate the following using nested IF statements (assuming thats the best way to do it): ¢ If the word count is zero then populate this cell with the value 0 ¢ If the word count is more than zero, for the first 500 words populate the cell with the number 3, for every subsequent 500 words add the number 1.5 to the initial 3. So if the wordcount 1000 is entered into A1 and the formula is in B1, the value in B1 would be 4.5. The value in B1 represents the number of hours it will take to write the article. Is this possible? If it is, can anyone help me with the formula? Many thanks Karl |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IFs and multiples
Should be
=IF(A1,IF(A1=500,3,0)+(INT(A1/500)-1)*1.5,0) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: =IF(A1=500,3,0)+(INT(A1/500)-1)*1.5 If this post helps click Yes --------------- Jacob Skaria "Karl" wrote: Hi, I work for a publishing company and Im designing a spreadsheet for our clients. The idea is that the client specifies a few variables and the spreadsheet will tell them how much the project will cost and how long it will take. One of things I want to do is have them enter the word count of the article they want written. In another cell, I want to put a formula that will check the word count and calculate the following using nested IF statements (assuming thats the best way to do it): ¢ If the word count is zero then populate this cell with the value 0 ¢ If the word count is more than zero, for the first 500 words populate the cell with the number 3, for every subsequent 500 words add the number 1.5 to the initial 3. So if the wordcount 1000 is entered into A1 and the formula is in B1, the value in B1 would be 4.5. The value in B1 represents the number of hours it will take to write the article. Is this possible? If it is, can anyone help me with the formula? Many thanks Karl |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IFs and multiples
Its again wrong...Try the below
=IF(A1,3,0)+IF(A1500,FLOOR(A1-500,500)/500,0)*1.5 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Should be =IF(A1,IF(A1=500,3,0)+(INT(A1/500)-1)*1.5,0) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: =IF(A1=500,3,0)+(INT(A1/500)-1)*1.5 If this post helps click Yes --------------- Jacob Skaria "Karl" wrote: Hi, I work for a publishing company and Im designing a spreadsheet for our clients. The idea is that the client specifies a few variables and the spreadsheet will tell them how much the project will cost and how long it will take. One of things I want to do is have them enter the word count of the article they want written. In another cell, I want to put a formula that will check the word count and calculate the following using nested IF statements (assuming thats the best way to do it): ¢ If the word count is zero then populate this cell with the value 0 ¢ If the word count is more than zero, for the first 500 words populate the cell with the number 3, for every subsequent 500 words add the number 1.5 to the initial 3. So if the wordcount 1000 is entered into A1 and the formula is in B1, the value in B1 would be 4.5. The value in B1 represents the number of hours it will take to write the article. Is this possible? If it is, can anyone help me with the formula? Many thanks Karl |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IFs and multiples
Hi,
You could use this as well. D12 is where you enter the time. D7:D8 holds 0 and 501. E7:E8 holds 3 and 4.5 =IF(D12=0,0,VLOOKUP(D12,$D$7:$E$8,2)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Karl" wrote in message ... Hi, I work for a publishing company and Im designing a spreadsheet for our clients. The idea is that the client specifies a few variables and the spreadsheet will tell them how much the project will cost and how long it will take. One of things I want to do is have them enter the word count of the article they want written. In another cell, I want to put a formula that will check the word count and calculate the following using nested IF statements (assuming thats the best way to do it): ¢ If the word count is zero then populate this cell with the value 0 ¢ If the word count is more than zero, for the first 500 words populate the cell with the number 3, for every subsequent 500 words add the number 1.5 to the initial 3. So if the wordcount 1000 is entered into A1 and the formula is in B1, the value in B1 would be 4.5. The value in B1 represents the number of hours it will take to write the article. Is this possible? If it is, can anyone help me with the formula? Many thanks Karl |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IFs and multiples
Hi,
Try this =IF(A1=0,0,1.5+1.5*ROUNDUP(A1/500,)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Karl" wrote: Hi, I work for a publishing company and Im designing a spreadsheet for our clients. The idea is that the client specifies a few variables and the spreadsheet will tell them how much the project will cost and how long it will take. One of things I want to do is have them enter the word count of the article they want written. In another cell, I want to put a formula that will check the word count and calculate the following using nested IF statements (assuming thats the best way to do it): ¢ If the word count is zero then populate this cell with the value 0 ¢ If the word count is more than zero, for the first 500 words populate the cell with the number 3, for every subsequent 500 words add the number 1.5 to the initial 3. So if the wordcount 1000 is entered into A1 and the formula is in B1, the value in B1 would be 4.5. The value in B1 represents the number of hours it will take to write the article. Is this possible? If it is, can anyone help me with the formula? Many thanks Karl |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IFs and multiples
Based on other responses I see that there are two ways to interpret your
question for the second use =IF(A1=0,0,1.5+1.5*INT(A1/500)) The question is what do you do when there are 1001 words and 1499? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Karl" wrote: Hi, I work for a publishing company and Im designing a spreadsheet for our clients. The idea is that the client specifies a few variables and the spreadsheet will tell them how much the project will cost and how long it will take. One of things I want to do is have them enter the word count of the article they want written. In another cell, I want to put a formula that will check the word count and calculate the following using nested IF statements (assuming thats the best way to do it): ¢ If the word count is zero then populate this cell with the value 0 ¢ If the word count is more than zero, for the first 500 words populate the cell with the number 3, for every subsequent 500 words add the number 1.5 to the initial 3. So if the wordcount 1000 is entered into A1 and the formula is in B1, the value in B1 would be 4.5. The value in B1 represents the number of hours it will take to write the article. Is this possible? If it is, can anyone help me with the formula? Many thanks Karl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying multiples of 10 | Excel Discussion (Misc queries) | |||
Displaying multiples of 10 | Excel Discussion (Misc queries) | |||
Displaying multiples of 10 | Excel Discussion (Misc queries) | |||
COUNTIF for multiples of 3 | Excel Worksheet Functions | |||
Multiples of 4 in an IF statement | Excel Worksheet Functions |