Microsoft Office Training London 020 79209500
You can use Excel’s ROMAN function in a formula whenever you need to express normal, Arabic numbers as Roman numerals.
Just type “=r” into a cell and you will see a list of Excel functions pop up, then double-click ROMAN in the list to start your formula. If you like using the keyboard then type-in “=ro” and press the TAB key.
Enter the reference of the cell containing the number you want converted, in this case it’s B3. Then press ENTER and you’re done. Excel’s ROMAN function converts positive numbers up to a maximum of 3999. The optional Form argument gives you a choice of up to four degrees of simplification of the classic Roman form. For example, the value of 2500 can not be simplified and is expressed as roman MMD. However, 2499 is MMCDXCIX in the classic form but MMID in the simplified form.
Enter your formula as =ROMAN(B3,FALSE) for the simplified form or use a number from 1 to 3 for the less simplified forms, i.e. =ROMAN(B3,2) gives you MMXDIX. Just enter the formula as =ROMAN(B3) if it can’t be simplified.
I’m glad we don’t have to use roman numerals in our Excel worksheets, it’s no wonder that Maximus was always in such a bad mood.
My name is Maximus Decimus Meridius. Father to a murdered son, husband to a murdered wife. And I will have my vengeance, in this life or the next.
If you’ve already got Roman numerals and you want to convert them to normal, Arabic numbers, then use the ARABIC function.
To learn more about the world of Excel functions, come along on our dedicated Excel Functions Course.
Call 020 7920 9500 now for details.
The SUMPRODUCT function is one of those hero functions that, once discovered, you wonder how you ever managed without. It does not just do “what it says on the tin”, there’s a lot more to it than that. In particular, it’s one of the most powerful and flexible filter functions in Excel. And so much better than SUMIF or SUMIFS.
It’s easy to calculate someone’s age from their date of birth if you know about Excel’s DATEDIF function, unfortunately it’s easy to miss this function as it is not documented. Excel will not help you fill in the DATEDIF function interval values, you need to see the list here.
Our Excel double click tricks are some of those little things that make your life so much easier. You probably know most of them already. Or do you? I think that anyone who uses Excel regularly should know them.
Complicated text formulas using either ampersands and the CONCATENATE function are the bane of our life. Not any more! Excel new text functions will really help us nail those text formulas. We’ll be looking at the CONCAT function and the TEXTJOIN function.
Usually the formulas you need for percentages and differences are quite straightforward: divisions for percentages and a minus sign to take one value from another. But there are pitfalls for the unwary which we shall explore.
I think the Excel FILTER function does the filter job better than AutoFilter. It’s a live formula and an extraction, you don’t have to filter your data in place. There’s no need for that clunky Advanced Filter…
