In Excel how do I arrange for the value of a cell to change based on the value of another where I need more...
I am creating a spreadsheet to make it easier to calculate an end figure.
If cell A1 = X value, I want cell A2 to change according to the value of A1,
So, if A1 reads as £100K, I want A2 to read as 0.1, if it reads as £200K I want A2 to read as 0.2 and so on. I have this in a table, but don't know to integrate it.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
edited Feb 15 at 13:31
Rich Michaels
1,2812210
asked Feb 15 at 10:45
WillWill
6
closed as unclear what you're asking by fixer1234, Máté Juhász, Excellll, Mike Fitzpatrick, BillP3rd Feb 18 at 20:28
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
I am creating a spreadsheet to make it easier to calculate an end figure.
If cell A1 = X value, I want cell A2 to change according to the value of A1,
So, if A1 reads as £100K, I want A2 to read as 0.1, if it reads as £200K I want A2 to read as 0.2 and so on. I have this in a table, but don't know to integrate it.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
edited Feb 15 at 13:31
Rich Michaels
1,2812210
asked Feb 15 at 10:45
WillWill
6
closed as unclear what you're asking by fixer1234, Máté Juhász, Excellll, Mike Fitzpatrick, BillP3rd Feb 18 at 20:28
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1
Please show some sample data including what you would like to achieve. There are many ways to achieve your example and IF() is one of them. We need more information in order to answer this questions properly.
– Kevin Anthony Oppegaard Rose
Feb 15 at 10:52
sounds like you want some kind of lookup formula
– RickyTillson
Feb 15 at 11:54
1
This is very clear to you, but for people who aren't looking at your spreadsheet or familiar with your task, it is very ambiguous. The right solution depends on a lot of details you don't cover. Please provide a complete example of what you need to do. It would also help if you describe the context of what you're doing.
– fixer1234
Feb 15 at 12:27
=A1/1000000? Is the text in A1100000or is it literally£100K? Perhaps=TRIM(MID(A2,2,SEARCH("K",A2)-2))/1000if it's literally the text...
– BruceWayne
Feb 15 at 15:13
add a comment |
I am creating a spreadsheet to make it easier to calculate an end figure.
If cell A1 = X value, I want cell A2 to change according to the value of A1,
So, if A1 reads as £100K, I want A2 to read as 0.1, if it reads as £200K I want A2 to read as 0.2 and so on. I have this in a table, but don't know to integrate it.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
edited Feb 15 at 13:31
Rich Michaels
1,2812210
asked Feb 15 at 10:45
WillWill
6
I am creating a spreadsheet to make it easier to calculate an end figure.
If cell A1 = X value, I want cell A2 to change according to the value of A1,
So, if A1 reads as £100K, I want A2 to read as 0.1, if it reads as £200K I want A2 to read as 0.2 and so on. I have this in a table, but don't know to integrate it.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
edited Feb 15 at 13:31
Rich Michaels
1,2812210
asked Feb 15 at 10:45
WillWill
6
edited Feb 15 at 13:31
Rich Michaels
1,2812210
asked Feb 15 at 10:45
WillWill
6
edited Feb 15 at 13:31
Rich Michaels
1,2812210
edited Feb 15 at 13:31
Rich Michaels
1,2812210
edited Feb 15 at 13:31
Rich Michaels
1,2812210
1,2812210
asked Feb 15 at 10:45
WillWill
6
asked Feb 15 at 10:45
WillWill
6
asked Feb 15 at 10:45
WillWill
6
6
closed as unclear what you're asking by fixer1234, Máté Juhász, Excellll, Mike Fitzpatrick, BillP3rd Feb 18 at 20:28
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as unclear what you're asking by fixer1234, Máté Juhász, Excellll, Mike Fitzpatrick, BillP3rd Feb 18 at 20:28
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1
Please show some sample data including what you would like to achieve. There are many ways to achieve your example and IF() is one of them. We need more information in order to answer this questions properly.
– Kevin Anthony Oppegaard Rose
Feb 15 at 10:52
sounds like you want some kind of lookup formula
– RickyTillson
Feb 15 at 11:54
1
This is very clear to you, but for people who aren't looking at your spreadsheet or familiar with your task, it is very ambiguous. The right solution depends on a lot of details you don't cover. Please provide a complete example of what you need to do. It would also help if you describe the context of what you're doing.
– fixer1234
Feb 15 at 12:27
=A1/1000000? Is the text in A1100000or is it literally£100K? Perhaps=TRIM(MID(A2,2,SEARCH("K",A2)-2))/1000if it's literally the text...
– BruceWayne
Feb 15 at 15:13
add a comment |
1
Please show some sample data including what you would like to achieve. There are many ways to achieve your example and IF() is one of them. We need more information in order to answer this questions properly.
– Kevin Anthony Oppegaard Rose
Feb 15 at 10:52
sounds like you want some kind of lookup formula
– RickyTillson
Feb 15 at 11:54
1
This is very clear to you, but for people who aren't looking at your spreadsheet or familiar with your task, it is very ambiguous. The right solution depends on a lot of details you don't cover. Please provide a complete example of what you need to do. It would also help if you describe the context of what you're doing.
– fixer1234
Feb 15 at 12:27
=A1/1000000? Is the text in A1100000or is it literally£100K? Perhaps=TRIM(MID(A2,2,SEARCH("K",A2)-2))/1000if it's literally the text...
– BruceWayne
Feb 15 at 15:13
1
1
Please show some sample data including what you would like to achieve. There are many ways to achieve your example and IF() is one of them. We need more information in order to answer this questions properly.
– Kevin Anthony Oppegaard Rose
Feb 15 at 10:52
Please show some sample data including what you would like to achieve. There are many ways to achieve your example and IF() is one of them. We need more information in order to answer this questions properly.
– Kevin Anthony Oppegaard Rose
Feb 15 at 10:52
sounds like you want some kind of lookup formula
– RickyTillson
Feb 15 at 11:54
sounds like you want some kind of lookup formula
– RickyTillson
Feb 15 at 11:54
1
1
This is very clear to you, but for people who aren't looking at your spreadsheet or familiar with your task, it is very ambiguous. The right solution depends on a lot of details you don't cover. Please provide a complete example of what you need to do. It would also help if you describe the context of what you're doing.
– fixer1234
Feb 15 at 12:27
This is very clear to you, but for people who aren't looking at your spreadsheet or familiar with your task, it is very ambiguous. The right solution depends on a lot of details you don't cover. Please provide a complete example of what you need to do. It would also help if you describe the context of what you're doing.
– fixer1234
Feb 15 at 12:27
=A1/1000000? Is the text in A1 100000 or is it literally £100K? Perhaps =TRIM(MID(A2,2,SEARCH("K",A2)-2))/1000 if it's literally the text...– BruceWayne
Feb 15 at 15:13
=A1/1000000? Is the text in A1 100000 or is it literally £100K? Perhaps =TRIM(MID(A2,2,SEARCH("K",A2)-2))/1000 if it's literally the text...– BruceWayne
Feb 15 at 15:13
add a comment |
1 Answer
1
active
oldest
votes
It depends on whether A1 contains a Number or a String value. If A1 contains a Number, then in A2 enter:
=A1/10^6
and apply the correct formatting, but if A1 contains a String value, then in A2 enter:
="0." & MID(A1,2,1)
If you want a formula that will work either way, then use:
=IFERROR(A1/10^6,"0."&MID(A1,2,1))
answered Feb 15 at 16:08
Gary's StudentGary's Student
13.9k31731
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It depends on whether A1 contains a Number or a String value. If A1 contains a Number, then in A2 enter:
=A1/10^6
and apply the correct formatting, but if A1 contains a String value, then in A2 enter:
="0." & MID(A1,2,1)
If you want a formula that will work either way, then use:
=IFERROR(A1/10^6,"0."&MID(A1,2,1))
answered Feb 15 at 16:08
Gary's StudentGary's Student
13.9k31731
add a comment |
It depends on whether A1 contains a Number or a String value. If A1 contains a Number, then in A2 enter:
=A1/10^6
and apply the correct formatting, but if A1 contains a String value, then in A2 enter:
="0." & MID(A1,2,1)
If you want a formula that will work either way, then use:
=IFERROR(A1/10^6,"0."&MID(A1,2,1))
answered Feb 15 at 16:08
Gary's StudentGary's Student
13.9k31731
add a comment |
It depends on whether A1 contains a Number or a String value. If A1 contains a Number, then in A2 enter:
=A1/10^6
and apply the correct formatting, but if A1 contains a String value, then in A2 enter:
="0." & MID(A1,2,1)
If you want a formula that will work either way, then use:
=IFERROR(A1/10^6,"0."&MID(A1,2,1))
answered Feb 15 at 16:08
Gary's StudentGary's Student
13.9k31731
It depends on whether A1 contains a Number or a String value. If A1 contains a Number, then in A2 enter:
=A1/10^6
and apply the correct formatting, but if A1 contains a String value, then in A2 enter:
="0." & MID(A1,2,1)
If you want a formula that will work either way, then use:
=IFERROR(A1/10^6,"0."&MID(A1,2,1))
answered Feb 15 at 16:08
Gary's StudentGary's Student
13.9k31731
edited Feb 15 at 16:25
answered Feb 15 at 16:08
Gary's StudentGary's Student
13.9k31731
answered Feb 15 at 16:08
Gary's StudentGary's Student
13.9k31731
answered Feb 15 at 16:08
Gary's StudentGary's Student
13.9k31731
13.9k31731
add a comment |
add a comment |
1
Please show some sample data including what you would like to achieve. There are many ways to achieve your example and IF() is one of them. We need more information in order to answer this questions properly.
– Kevin Anthony Oppegaard Rose
Feb 15 at 10:52
sounds like you want some kind of lookup formula
– RickyTillson
Feb 15 at 11:54
1
This is very clear to you, but for people who aren't looking at your spreadsheet or familiar with your task, it is very ambiguous. The right solution depends on a lot of details you don't cover. Please provide a complete example of what you need to do. It would also help if you describe the context of what you're doing.
– fixer1234
Feb 15 at 12:27
=A1/1000000? Is the text in A1100000or is it literally£100K? Perhaps=TRIM(MID(A2,2,SEARCH("K",A2)-2))/1000if it's literally the text...– BruceWayne
Feb 15 at 15:13