Contextures

Contextures News 20190716

Excel Number Format Trick

July 16, 2019

Formula challenge, number formatting, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

You'll get my next newsletter in 2 weeks -- we're on the summer schedule now.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Number Format Trick

If you have a column of numbers in Excel, and want to line them up at their decimal points, you can use the Number format, with a set number of decimal places showing.

number format decimals

However, that format adds zeros, so you can't easily see how many decimal places the original numbers have. For a cleaner view of the numbers, create a custom number format, with question marks after the decimal point.

For example, custom format -- #,##0.??? -- will show up to 3 numbers after the decimal point, with no zeros added. This example is in this week's sample file (below)

custom number format decimals

P. S. I've also had problems lining up currency symbols, in Accounting Format. See the solution to that problem on my Contextures blog.

Formula Challenge

We usually put one value in each cell on a worksheet, but my website shows you how to choose multiple items from a data validation drop down.

But after you create multi-value cells, how can you work with them? For example, a hotel rents rooms, with a different rate for each weekday. If a guest stays Sunday (40) and Monday (50) , their total cost would be 90.

What formula would you put in column D, to calculate the cost in each row, based on the days selected in column C? You can download the sample file (below) with the challenge, and my solution is below the screen shot.

If you'd like a hint, there's a similar example in the Count Numbers in Numbers section here.

calculate multiple item total

To calculate the line totals, I combined 3 functions:

=SUMPRODUCT(--ISNUMBER( SEARCH($F$2:$F$8,C2)), $G$2:$G$8)

  • The SEARCH function checks the list of day names, and returns a number if found in cell C2.
    • "Sun" is found at position 1 in cell C2, and "Mon" starts at position 6

calculate multiple item total

  • The ISNUMBER function returns TRUE for each number, and FALSE if not a number.
  • Then, the two minus signs (double unary) change TRUE to 1, and FALSE to zero.
  • The costs in G2:G8 are multiplied by the 1s and zeros
  • Finally, SUMPRODUCT calculates the total -- (1*40)+(1*50)=90.

calculate multiple item total

Sample File

Click here to get the sample Excel file for this week's newsletter. It has the number formatting sample, the formula challenge, and the formula solution. The zipped file is in xlsx format, with no macros.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Charts - Dave Paradi shows how he created an Excel chart with income inflows and outflows. It looks like a sideways waterfall chart (Level - Int)

Formula - Can you guess how Kevin Lehrbass sums the overlap of two ranges, before you read his article? (Level - All)

Excel Tips: You probably know most of the 22 Excel Tips in PC Mag's article, but maybe you've forgotten to use some of them recently, like making chart templates. (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Hidden Feature

I've had this mouse for a couple of years, and use it every day. It works well, and I love using the scroll wheel to move around in Excel. But last week, I accidentally clicked on the little white logo, and finally discovered another trick -- that logo opens the Windows Start menu!

Does your mouse (or another gadget) have hidden features that it took you a while to discover? Do you ever read the little booklet that comes with a new gadget? Obviously, I don't!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190716ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: July 13, 2019 3:34 PM