| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Specific formula query
Excel 2003
I want a cell to show a different value depending on whether another cell shows less than, equal to, or more than a range of values. For example: If value in cell X is 16 or less, I want cell Y to show 1 If value in cell X is more than 16, but less than or equal to 32, I want cell Y to show 2 If value in cell X is more than 32, but less than or equal to 48, I want cell Y to show 3 And so on... Help please, can't seem to create the right syntax to make this happen... Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Specific formula query
Here's one way of doing it:
=INT(CEILING(X,16)/16) Put the formula in your Y cell, changing X to an appropriate cell reference. Hope this helps. Pete On Dec 5, 7:15 pm, TomL77 wrote: Excel 2003 I want a cell to show a different value depending on whether another cell shows less than, equal to, or more than a range of values. For example: If value in cell X is 16 or less, I want cell Y to show 1 If value in cell X is more than 16, but less than or equal to 32, I want cell Y to show 2 If value in cell X is more than 32, but less than or equal to 48, I want cell Y to show 3 And so on... Help please, can't seem to create the right syntax to make this happen... Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Specific formula query
=ROUNDUP(A1/16,0)
-- Gary''s Student - gsnu200760 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Specific formula query
Cheers Pete, that's a start but I should have been more specific:
If value in cell X is 16 or less, I want cell Y to show value "1" AND if value in cell X is more than 16, but less than or equal to 32, I want cell Y to show value "2" AND if value in cell X is more than 32, but less than or equal to 48, I want cell Y to show value "3", and so on... (Cell X being the same cell on the same spreadsheet all the way through) Does this clarify? "Pete_UK" wrote: Here's one way of doing it: =INT(CEILING(X,16)/16) Put the formula in your Y cell, changing X to an appropriate cell reference. Hope this helps. Pete On Dec 5, 7:15 pm, TomL77 wrote: Excel 2003 I want a cell to show a different value depending on whether another cell shows less than, equal to, or more than a range of values. For example: If value in cell X is 16 or less, I want cell Y to show 1 If value in cell X is more than 16, but less than or equal to 32, I want cell Y to show 2 If value in cell X is more than 32, but less than or equal to 48, I want cell Y to show 3 And so on... Help please, can't seem to create the right syntax to make this happen... Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Specific formula query
One way
=CEILING(A1/16,1) or if you want to get more complicated have a look at creating a table and use the vlookup function. -- Greetings from New Zealand Bill Kuunders "TomL77" wrote in message ... Excel 2003 I want a cell to show a different value depending on whether another cell shows less than, equal to, or more than a range of values. For example: If value in cell X is 16 or less, I want cell Y to show 1 If value in cell X is more than 16, but less than or equal to 32, I want cell Y to show 2 If value in cell X is more than 32, but less than or equal to 48, I want cell Y to show 3 And so on... Help please, can't seem to create the right syntax to make this happen... Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Specific formula query
Yes, well put different values in X and see what you get out of the
formula! I don't understand how you can have the ...."value in cell X is 16 or less ... AND if value in cell X is more than 16 ..." at the same time. Are you refering to a range of cells, eg from A1 to A10, and so you could have different values in some of those cells? What is the cell reference of cell X and cell Y ? Pete On Dec 5, 7:36 pm, TomL77 wrote: Cheers Pete, that's a start but I should have been more specific: If value in cell X is 16 or less, I want cell Y to show value "1" AND if value in cell X is more than 16, but less than or equal to 32, I want cell Y to show value "2" AND if value in cell X is more than 32, but less than or equal to 48, I want cell Y to show value "3", and so on... (Cell X being the same cell on the same spreadsheet all the way through) Does this clarify? "Pete_UK" wrote: Here's one way of doing it: =INT(CEILING(X,16)/16) Put the formula in your Y cell, changing X to an appropriate cell reference. Hope this helps. Pete On Dec 5, 7:15 pm, TomL77 wrote: Excel 2003 I want a cell to show a different value depending on whether another cell shows less than, equal to, or more than a range of values. For example: If value in cell X is 16 or less, I want cell Y to show 1 If value in cell X is more than 16, but less than or equal to 32, I want cell Y to show 2 If value in cell X is more than 32, but less than or equal to 48, I want cell Y to show 3 And so on... Help please, can't seem to create the right syntax to make this happen... Thanks- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Specific formula query
Ah, perhaps I'm attempting something that can't be done?
Basically I want cell X to recognise when the value in cell Y shows any value in the range 0-16 (and show 1 as a result) AND recognise when it shows any value from 16-32 (and show 2 as a result) AND recognise when it shows any value from 32-48 (and show 3 as a result) and so on... Any clearer? Sorry... "Pete_UK" wrote: Yes, well put different values in X and see what you get out of the formula! I don't understand how you can have the ...."value in cell X is 16 or less ... AND if value in cell X is more than 16 ..." at the same time. Are you refering to a range of cells, eg from A1 to A10, and so you could have different values in some of those cells? What is the cell reference of cell X and cell Y ? Pete On Dec 5, 7:36 pm, TomL77 wrote: Cheers Pete, that's a start but I should have been more specific: If value in cell X is 16 or less, I want cell Y to show value "1" AND if value in cell X is more than 16, but less than or equal to 32, I want cell Y to show value "2" AND if value in cell X is more than 32, but less than or equal to 48, I want cell Y to show value "3", and so on... (Cell X being the same cell on the same spreadsheet all the way through) Does this clarify? "Pete_UK" wrote: Here's one way of doing it: =INT(CEILING(X,16)/16) Put the formula in your Y cell, changing X to an appropriate cell reference. Hope this helps. Pete On Dec 5, 7:15 pm, TomL77 wrote: Excel 2003 I want a cell to show a different value depending on whether another cell shows less than, equal to, or more than a range of values. For example: If value in cell X is 16 or less, I want cell Y to show 1 If value in cell X is more than 16, but less than or equal to 32, I want cell Y to show 2 If value in cell X is more than 32, but less than or equal to 48, I want cell Y to show 3 And so on... Help please, can't seem to create the right syntax to make this happen... Thanks- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Specific formula query
That is what the formula does, although you have now swapped around X
and Y in your description. Suppose cell X is A1, and this can take any positive value, and suppose B1 is your original Y, and you want this to show 1, 2, 3 etc depending on the value of A1. Put 5 in A1 and this formula in B1: =INT(CEILING(A1,16)/16) You should see 1 in B1, because A1 is less than 16. Put 16 in A1, and B1 will show 1 still. Put 17 in A1 and B1 will now show 2, because A1 (your cell X) is between 16 and 32. You can put 30 in A1, and B1 will still show 2. Change A1 to 39, and B1 will show 3, because A1 is between 32 and 48. Change A1 again to 10, and B1 will show 1. Isn't this what you want? By the way, if A1 is empty or contains 0 then the formula will return 0 in B1. If you want B1 to show 1 in this case, then change the formula to: =MAX(1,INT(CEILING(A1,16)/16)) Hope this helps. Pete On Dec 5, 8:06 pm, TomL77 wrote: Ah, perhaps I'm attempting something that can't be done? Basically I want cell X to recognise when the value in cell Y shows any value in the range 0-16 (and show 1 as a result) AND recognise when it shows any value from 16-32 (and show 2 as a result) AND recognise when it shows any value from 32-48 (and show 3 as a result) and so on... Any clearer? Sorry... "Pete_UK" wrote: Yes, well put different values in X and see what you get out of the formula! I don't understand how you can have the ...."value in cell X is 16 or less ... AND if value in cell X is more than 16 ..." at the same time. Are you refering to a range of cells, eg from A1 to A10, and so you could have different values in some of those cells? What is the cell reference of cell X and cell Y ? Pete On Dec 5, 7:36 pm, TomL77 wrote: Cheers Pete, that's a start but I should have been more specific: If value in cell X is 16 or less, I want cell Y to show value "1" AND if value in cell X is more than 16, but less than or equal to 32, I want cell Y to show value "2" AND if value in cell X is more than 32, but less than or equal to 48, I want cell Y to show value "3", and so on... (Cell X being the same cell on the same spreadsheet all the way through) Does this clarify? "Pete_UK" wrote: Here's one way of doing it: =INT(CEILING(X,16)/16) Put the formula in your Y cell, changing X to an appropriate cell reference. Hope this helps. Pete On Dec 5, 7:15 pm, TomL77 wrote: Excel 2003 I want a cell to show a different value depending on whether another cell shows less than, equal to, or more than a range of values. For example: If value in cell X is 16 or less, I want cell Y to show 1 If value in cell X is more than 16, but less than or equal to 32, I want cell Y to show 2 If value in cell X is more than 32, but less than or equal to 48, I want cell Y to show 3 And so on... Help please, can't seem to create the right syntax to make this happen... Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| query formula | Excel Discussion (Misc queries) | |||
| Query Items Listed During a Specific Time Period | Excel Discussion (Misc queries) | |||
| formula Query | Excel Discussion (Misc queries) | |||
| Query into specific columns | Excel Discussion (Misc queries) | |||
| Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) | |||
Linear Mode
