In this blog post, we look at how to pick a name at random from a list. There are many reasons you may need this kind of random selection from a list.
It may be a neat way of deciding who is next to do the washing up, or to make a cup of tea :). Or it is useful for randomly drawing teams for a knockout cup competition.
To perform this random selection, we are going to use two different functions together. They are INDEX and RANDBETWEEN.
The Excel TEXT function is used to convert a numeric value to text in a specified number format. This function is really useful when concatenating strings that will contain a numeric value.
The TEXT function in Excel requires two arguments – the text that needs formatting, and the format you want to use.
=TEXT(value, format_text)
It is very useful for displaying numbers that are used in text strings still in their number format. Examples of this include when using summary text on reports or in chart labels such as “Total sales this quarter: £4,073”.
[Read more…] about Excel Text Function with ExamplesWhen creating a timesheet in Excel you may need to calculate different rates of pay. This could be because of overtime, or the type of work being done.
In this tutorial, we create a timesheet that uses different rates of pay for working weekends, and also night shifts.
In this timesheet, the day and night shifts are entered on separate rows. This will make it easier for our calculations. A column (column E) is also used to record whether it is a day or night shift.
Our first task is to calculate the number of hours worked on the shift. To do this the following formula can be used. This formula finds the difference between the two times as a decimal, and then multiplies by 24 to convert it to hours.
(D5-B5)*24
If the shift is worked overnight, so you started work on one day but finished the next, then the formula below is used.
(1+D5-B5)*24
Now in this timesheet it is not quite that straight forward. We need to test first what shift they are doing and whether it was worked overnight or not, so that the correct formula can be run.
The formula below is entered into column F to calculate hours worked on the day shift.
=IF(E5="Day",(D5-B5)*24,0)
In column G, the formula below was used to calculate hours worked on the night shift. The IF function first tests if it was a night shift, then test to see if it was worked overnight (A5<C5), and runs the correct calculation as a result.
=IF(E5="Night",IF(A5<C5,(1+D5-B5)*24,(D5-B5)*24),0)[Read more…] about Excel Timesheet with Different Rates for Shift Work
The COUNTIF function is one of the most useful functions in Excel. Its job is to provide conditional counting. This is primarily used for analysing data and producing aggregates for reports and dashboards. However, there are many cool COUNTIF function examples in Excel
In this blog post, we will look into 5 more unorthodox but useful scenarios for the COUNTIF function to be used. The 5 COUNTIF function examples we look at are;
You may have scenario on a spreadsheet when you want to sum negative values only in a range.
Take the example below for instance, it contains a list of incoming and outgoing payments. If we needed the total for outgoings, we would need to sum the negative values only.
This can be done using the SUMIF function. This function allows us to sum only the values that meet a specific condition.
The formula below will total the outgoings in the list by summing only the values less than 0.
=SUMIF(B2:B12,"<0")
The criteria has been entered as text in double quotes. When entering criteria like this into the formula it needs to be entered as text.
The criteria specifies that only values less than 0 should be summed.
