excelbloggs
New Member
- Joined
- Dec 7, 2021
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hello,
I have three columns, two of which have data and one that needs to output an answer to an equation. The current setup is like this:
In the Finished column, I am simply wanting to do A2 * B2 to give the result you see in the Finished column but I know the 'm²' / 'm¹' is causing issues and I simply get '#VALUE'.
I tried to use Substitute and was able to get the correct answer, but it is not dynamic as I have only defined 'm²' so I can't copy the cells down properly and it also does not append the 'm²' back to the result.
=SUBSTITUTE(A2, "m²","")*B2
This gives me the result of '64' but ideally it would output '64m²'
Does anyone have any suggestions for an equation that will essentially be able to:
I have three columns, two of which have data and one that needs to output an answer to an equation. The current setup is like this:
Space | Completion | Finished |
64m² | 100% | 64m² |
20m¹ | 40% | 8m¹ |
In the Finished column, I am simply wanting to do A2 * B2 to give the result you see in the Finished column but I know the 'm²' / 'm¹' is causing issues and I simply get '#VALUE'.
I tried to use Substitute and was able to get the correct answer, but it is not dynamic as I have only defined 'm²' so I can't copy the cells down properly and it also does not append the 'm²' back to the result.
=SUBSTITUTE(A2, "m²","")*B2
This gives me the result of '64' but ideally it would output '64m²'
Does anyone have any suggestions for an equation that will essentially be able to:
- Check whether column A contains 'm¹' or 'm²' and ignore them
- Multiply by the value in the B column of the same row
- Add the 'm¹' or 'm²' back to the result