If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formulas
I need to know how I would write the formula for this function, if anyone
could help me that would be wonderful If a number in a cell is between 1-15 then I need it in another cell to only equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. These are 15 minutes increments of time. hard to explain, but in one cell will be the time in minutes for example 18 minutes, in another cell will be number that is a base unit for example 5. I need the number in the third cell to be the base number of units which is 5, and the time units from the other cell which since it is 18 would actually equal two time units, so 18=2, then the total number in the third cell will be 7 (5+2=7), but I need the spreadsheet to calculate the Time minutes of 18 into two units of time. Can anyone help me with this one is seems complicated. Thank you so much |
#2
|
|||
|
|||
Conditional Formulas
If your number of minutes is in cell A1, and you want how many whole or part
multiples of 15 minutes that is in B1, then type this in B1: =int((A1-1)/15)+1 Sam "Wendy" wrote: I need to know how I would write the formula for this function, if anyone could help me that would be wonderful If a number in a cell is between 1-15 then I need it in another cell to only equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. These are 15 minutes increments of time. hard to explain, but in one cell will be the time in minutes for example 18 minutes, in another cell will be number that is a base unit for example 5. I need the number in the third cell to be the base number of units which is 5, and the time units from the other cell which since it is 18 would actually equal two time units, so 18=2, then the total number in the third cell will be 7 (5+2=7), but I need the spreadsheet to calculate the Time minutes of 18 into two units of time. Can anyone help me with this one is seems complicated. Thank you so much |
#3
|
|||
|
|||
Conditional Formulas
RESTATED as: If a number in cell A1 is between 1-15 then I need cell B1 to
equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. You could write a nice long IF statement, or use a lookup function; both are considered 'conditional' But this is the simplest solution I can think of : If you number is in A1 then use =CEILING(A1,15)/15 This is not really "conditional" but serves your purpose BUT this assume that A1 hold a simple number 1, 16, 35 If A1 holds time (0:01, 0:16, 0:35( then change the formula to =CEILING(A1*24*60,15)/15 Excel stores time as a fraction of a day (we multiple this fraction by 24 to get hours and again by 60 to get minutes) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Wendy" wrote in message ... I need to know how I would write the formula for this function, if anyone could help me that would be wonderful If a number in a cell is between 1-15 then I need it in another cell to only equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. These are 15 minutes increments of time. hard to explain, but in one cell will be the time in minutes for example 18 minutes, in another cell will be number that is a base unit for example 5. I need the number in the third cell to be the base number of units which is 5, and the time units from the other cell which since it is 18 would actually equal two time units, so 18=2, then the total number in the third cell will be 7 (5+2=7), but I need the spreadsheet to calculate the Time minutes of 18 into two units of time. Can anyone help me with this one is seems complicated. Thank you so much |
#4
|
|||
|
|||
Conditional Formulas
Thank you so much, that was easy enough. perfect just what I am looking for!!!
"Sam Wilson" wrote: If your number of minutes is in cell A1, and you want how many whole or part multiples of 15 minutes that is in B1, then type this in B1: =int((A1-1)/15)+1 Sam "Wendy" wrote: I need to know how I would write the formula for this function, if anyone could help me that would be wonderful If a number in a cell is between 1-15 then I need it in another cell to only equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. These are 15 minutes increments of time. hard to explain, but in one cell will be the time in minutes for example 18 minutes, in another cell will be number that is a base unit for example 5. I need the number in the third cell to be the base number of units which is 5, and the time units from the other cell which since it is 18 would actually equal two time units, so 18=2, then the total number in the third cell will be 7 (5+2=7), but I need the spreadsheet to calculate the Time minutes of 18 into two units of time. Can anyone help me with this one is seems complicated. Thank you so much |
#5
|
|||
|
|||
Conditional Formulas
Okay thank you, the numbers in cell A1 would be a simple number, But here is
the thing. for example cell A1 would be 18, Cell B1 would be 5. in cell C1 I would need the sum of cell A1 and B1, however the conversion of the minutes from cell A1 would need to be there. For example 18 minutes equeal 2 units, so I need the sum in cell C1 to be the base number of 5 from cell B1 and the converted time from cell A1 (which would be 2) so the sum in cell C1 would be 7. can i add that formula to in Cell C1 like this =B1+=CEILING(A1,15)/15? or how would that work? "Bernard Liengme" wrote: RESTATED as: If a number in cell A1 is between 1-15 then I need cell B1 to equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. You could write a nice long IF statement, or use a lookup function; both are considered 'conditional' But this is the simplest solution I can think of : If you number is in A1 then use =CEILING(A1,15)/15 This is not really "conditional" but serves your purpose BUT this assume that A1 hold a simple number 1, 16, 35 If A1 holds time (0:01, 0:16, 0:35( then change the formula to =CEILING(A1*24*60,15)/15 Excel stores time as a fraction of a day (we multiple this fraction by 24 to get hours and again by 60 to get minutes) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Wendy" wrote in message ... I need to know how I would write the formula for this function, if anyone could help me that would be wonderful If a number in a cell is between 1-15 then I need it in another cell to only equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. These are 15 minutes increments of time. hard to explain, but in one cell will be the time in minutes for example 18 minutes, in another cell will be number that is a base unit for example 5. I need the number in the third cell to be the base number of units which is 5, and the time units from the other cell which since it is 18 would actually equal two time units, so 18=2, then the total number in the third cell will be 7 (5+2=7), but I need the spreadsheet to calculate the Time minutes of 18 into two units of time. Can anyone help me with this one is seems complicated. Thank you so much |
#6
|
|||
|
|||
Conditional Formulas
Very close; you need =B1+CEILING(A1,15)/15
An Excel formula has only one = (unless you are making an equality test) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Wendy" wrote in message ... Okay thank you, the numbers in cell A1 would be a simple number, But here is the thing. for example cell A1 would be 18, Cell B1 would be 5. in cell C1 I would need the sum of cell A1 and B1, however the conversion of the minutes from cell A1 would need to be there. For example 18 minutes equeal 2 units, so I need the sum in cell C1 to be the base number of 5 from cell B1 and the converted time from cell A1 (which would be 2) so the sum in cell C1 would be 7. can i add that formula to in Cell C1 like this =B1+=CEILING(A1,15)/15? or how would that work? "Bernard Liengme" wrote: RESTATED as: If a number in cell A1 is between 1-15 then I need cell B1 to equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. You could write a nice long IF statement, or use a lookup function; both are considered 'conditional' But this is the simplest solution I can think of : If you number is in A1 then use =CEILING(A1,15)/15 This is not really "conditional" but serves your purpose BUT this assume that A1 hold a simple number 1, 16, 35 If A1 holds time (0:01, 0:16, 0:35( then change the formula to =CEILING(A1*24*60,15)/15 Excel stores time as a fraction of a day (we multiple this fraction by 24 to get hours and again by 60 to get minutes) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Wendy" wrote in message ... I need to know how I would write the formula for this function, if anyone could help me that would be wonderful If a number in a cell is between 1-15 then I need it in another cell to only equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. These are 15 minutes increments of time. hard to explain, but in one cell will be the time in minutes for example 18 minutes, in another cell will be number that is a base unit for example 5. I need the number in the third cell to be the base number of units which is 5, and the time units from the other cell which since it is 18 would actually equal two time units, so 18=2, then the total number in the third cell will be 7 (5+2=7), but I need the spreadsheet to calculate the Time minutes of 18 into two units of time. Can anyone help me with this one is seems complicated. Thank you so much |
#7
|
|||
|
|||
Conditional Formulas
Yes thank you, I figured out I had it backwards, and it is working perfect!
Thanks "Bernard Liengme" wrote: Very close; you need =B1+CEILING(A1,15)/15 An Excel formula has only one = (unless you are making an equality test) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Wendy" wrote in message ... Okay thank you, the numbers in cell A1 would be a simple number, But here is the thing. for example cell A1 would be 18, Cell B1 would be 5. in cell C1 I would need the sum of cell A1 and B1, however the conversion of the minutes from cell A1 would need to be there. For example 18 minutes equeal 2 units, so I need the sum in cell C1 to be the base number of 5 from cell B1 and the converted time from cell A1 (which would be 2) so the sum in cell C1 would be 7. can i add that formula to in Cell C1 like this =B1+=CEILING(A1,15)/15? or how would that work? "Bernard Liengme" wrote: RESTATED as: If a number in cell A1 is between 1-15 then I need cell B1 to equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. You could write a nice long IF statement, or use a lookup function; both are considered 'conditional' But this is the simplest solution I can think of : If you number is in A1 then use =CEILING(A1,15)/15 This is not really "conditional" but serves your purpose BUT this assume that A1 hold a simple number 1, 16, 35 If A1 holds time (0:01, 0:16, 0:35( then change the formula to =CEILING(A1*24*60,15)/15 Excel stores time as a fraction of a day (we multiple this fraction by 24 to get hours and again by 60 to get minutes) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Wendy" wrote in message ... I need to know how I would write the formula for this function, if anyone could help me that would be wonderful If a number in a cell is between 1-15 then I need it in another cell to only equal 1, if the number is between 16-30 I need it to equal 2, if the number is 31-45 I need it to equal 3, if the number is 46-60 I need it to equal 4, if the number is between 61-75 I need it to equal 5, so on and so fourth. These are 15 minutes increments of time. hard to explain, but in one cell will be the time in minutes for example 18 minutes, in another cell will be number that is a base unit for example 5. I need the number in the third cell to be the base number of units which is 5, and the time units from the other cell which since it is 18 would actually equal two time units, so 18=2, then the total number in the third cell will be 7 (5+2=7), but I need the spreadsheet to calculate the Time minutes of 18 into two units of time. Can anyone help me with this one is seems complicated. Thank you so much |
Thread Tools | |
Display Modes | |
|
|