Excel if statement MB/KB to GB

Question

Excel if statement MB/KB to GB

AsiekG 41

Hi,

See table below:

| Value | Type |

| 21 | MB |

| 916 | MB |

| 1.91 | GB |

| 1.02 | GB |

| 6 | MB |

| 659 | kB |

| 101 | MB |

| 47 | MB |

| 487 | MB |

| 1.46 | GB |

| 829 | MB |

| 556 | kB |

| 211 | MB |

| 291 | MB |

| 836 | MB |

| 796 | MB |

| 1.39 | GB |

| 841 | MB |

| 784 | kB |

| 16 | MB |

| 18 | MB |

| 6 | MB |

| 1.39 | GB |

| 288 | MB |

| 128 | MB |

| 246 | MB |

| 232 | MB |

| 18 | MB |

| 1.52 | GB |

| 1.49 | GB |

I am trying to convert each MB and KB to GB. Now this is only 30 cells, so that could be done by doing them one by one.. But I need to do it for at least 300 cells more.. What I want to achieve is that if a cell contains a MB or KB value, convert it to a GB value. I tried different search, IF, OR, ISNUMBER combinations but unfortunately I'm not really an Excel expert.. Can anyone help me with this?

Thank you.

Barry Schwarz 5,431 Reputation points

2023-06-01T19:47:37+00:00
Would something like

IFS(Type="MB",Newvalue=Value/1000, Type="KB",Newvalue=Value/1000000, TRUE, Newvalue=Value)

do what you want.
Barry Schwarz 5,431 Reputation points

2023-06-01T19:55:16.58+00:00

Would something like

IFS(Type="MB",Newvalue=Value/1000,

Type="KB",Newvalue=Value/1000000,

TRUE, Newvalue=Value)

do what you want?
AsiekG 41 Reputation points

2023-06-02T07:57:41.78+00:00
Hi,

So I tried, but probably due to region setting the commas are not working for me, so I needed to use semicolons. But I now get the #NAME error:

See your code changed below.

Also where does the IFS statement apply to? I cannot select in which column it should search for the value type (MB/KB) and on which column it should apply the calculation ..

Thank you so far.

=IFS(Type="MB";Newvalue=Value/1000; Type="KB";Newvalue=Value/1000000; TRUE;Newvalue=Value)
AllenXu-MSFT 24,991 Reputation points Moderator

2023-06-05T01:59:51.58+00:00

@AsiekG,

I have posted a screenshot which records the process. Please take a reference to it.

2 answers

Your answer

Barry Schwarz 5,431 Reputation points

2023-06-01T19:47:37+00:00

Would something like

IFS(Type="MB",Newvalue=Value/1000, Type="KB",Newvalue=Value/1000000, TRUE, Newvalue=Value)

do what you want.
Barry Schwarz 5,431 Reputation points

2023-06-01T19:55:16.58+00:00

Would something like

IFS(Type="MB",Newvalue=Value/1000,

Type="KB",Newvalue=Value/1000000,

TRUE, Newvalue=Value)

do what you want?
AsiekG 41 Reputation points

2023-06-02T07:57:41.78+00:00

Hi,

So I tried, but probably due to region setting the commas are not working for me, so I needed to use semicolons. But I now get the #NAME error:

See your code changed below.

Also where does the IFS statement apply to? I cannot select in which column it should search for the value type (MB/KB) and on which column it should apply the calculation ..

Thank you so far.

=IFS(Type="MB";Newvalue=Value/1000; Type="KB";Newvalue=Value/1000000; TRUE;Newvalue=Value)
AllenXu-MSFT 24,991 Reputation points Moderator

2023-06-05T01:59:51.58+00:00

@AsiekG,

I have posted a screenshot which records the process. Please take a reference to it.

Answer 1

AllenXu-MSFT 24,991 Moderator

Hi @AsiekG,

Add a new column to the table, and use the formula like below:

=IFS(B2="MB",A2/1024,B2="KB",A2/1048576,TRUE,A2)

FORMULA

Copy the values of new column and paste to the "Value" column.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

0 comments

Answer 2

Hi,

You can use a combination of formulas in Excel to convert the values in MB and kB to GB:

Assuming your values are in the "Value" column and the corresponding types are in the "Type" column, you can add a new column (let's say it's column C) to calculate the converted values.
In cell C2, enter the following formula:

=IF(B2="MB",A2/1024,IF(B2="kB",A2/1024/1024,A2))

Drag the formula down to apply it to the rest of the cells in column C.
Format the cells in column C as desired to display the values in GB. Select the cells, right-click, choose "Format Cells," go to the "Number" tab, and select "Gigabyte (GB)" as the format.

Now, the values in column C will represent the converted values in GB. If a cell in the "Type" column contains "MB," it will be divided by 1024 to convert it to GB. If it contains "kB," it will be divided by 1024 twice (1024 * 1024) to convert it to GB. Otherwise, the value remains the same.

Note: Make sure to use the correct cell references based on your specific data layout. If your data is in a different range or columns, adjust the formulas accordingly.

Best Regards.

Share via

Excel if statement MB/KB to GB

2 answers

Your answer