If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Wednesday, April 22, 2015

Using “Playbill” font & Excel formula for incell charts



Using “Playbill” font & Excel formula for incell charts
With REPT formula and symbol (“|”), we can make in-cell charts. And to look them just like a chart, use the playbill font. We can change the theme color as well to make it more prominent/attractive.

For example, our data is in range D3:D7. Use the below formula. Here we are repeating the symbol and the number of times is the cell value divided by the maximum number of the list/range and multiplied by hundred. It’s easy & simple, just try it.

=REPT("|",D3/MAX($D$3:$D$7)*100)


Friday, April 17, 2015

Excel formula: COUNTIFS() with date range in cell reference

Excel formula: COUNTIFS() with date range in cell reference
Suppose we have soma data with dates and we want to count the rows, falling between two dates.  I had to do the same thing today and faced an issue. Though most of the people would be already aware of it but for a person like me, it was a new thing so thought to share with everyone.
If we do the date comparison this way “>=G1”, here G1 is the cell where start date is written then this check will not work. Though, it will work, if we specify a date itself instead of cell reference. For cell reference, the comparison operator will be in double quotes and with & we will add the cell reference. Please see the complete syntax. Here the B:B is the column range/criteria range having dates and G1 & H1 are start & end dates.
=COUNTIFS(B:B,">="&G1,B:B,"<="&H1)