I know some people reply to a request email with "Will do.". This is better than nothing. But it depends on the time needed to do the job. If it takes you hours to complete the job and you won't be able to get it done today, yes please respond "Will do." (or better with the estimated timeframe). But if it's something that only takes you a few minutes to do it (like sending a bank statement to another staff or checking the payment status of a vendor's invoice), please do the job first and then reply to the email with "done" or the requested info) so it's more efficient in your emailing with them... rather than saying "will do" but your response never arrived in their inbox or you would end up replying to their question AFTER they chase you with another email.
Sunday, May 12, 2024
The Ultimate Email Etiquettes
I know some people reply to a request email with "Will do.". This is better than nothing. But it depends on the time needed to do the job. If it takes you hours to complete the job and you won't be able to get it done today, yes please respond "Will do." (or better with the estimated timeframe). But if it's something that only takes you a few minutes to do it (like sending a bank statement to another staff or checking the payment status of a vendor's invoice), please do the job first and then reply to the email with "done" or the requested info) so it's more efficient in your emailing with them... rather than saying "will do" but your response never arrived in their inbox or you would end up replying to their question AFTER they chase you with another email.
Thursday, May 19, 2011
Managing Data Analysis with Multiple Conditions
In Excel, when you need to retrieve data off a table based on a criterion, you can easily use a VLOOKUP or HLOOKUP formula. If you ever want to sum the entire data which meets that single criterio, you use SUMIF in place of VLOOKUP.
Now say you have a great big table of downloaded report (called "SourceData" tabsheet) detailing both sales and expenses information by Cost Centre and Department for the whole year of 2011. On another worksheet that you call "Summary", $A10 is the first criteria (say Cost Centre of your organisation) that is listed down from cell A10 to A30. B$3 is the second criteria (say Department of your organisation) that you placed horizontally from cell B3 to B8.
And so it goes that you need to sum up the data based on multiple criteria. Yes, you can use SUMIFS that unfolds like =SUMIFS(SourceData!$K$2:$K$1000,SourceData!$E$2:$E$1000,$A10,SourceData!$D$2:$D$1000,B$3). But what if you're still using the older versions of Excel (prior to 2007) such as Office 2003, 2002 (XP), or even 2000? You don't have the luxury of SUMIFS function yet. The solution is to utilise the "array flavored" combination of SUM and IF.
Using the same logic of working as SUMIFS, this array formula will generate the same results. It goes like this {=SUM(IF((SourceData!$E$2:$E$1000=$A10)*(SourceData!$D$2:$D$1000=B$3),SourceData!$K$2:$K$1000))}. The uniqueness (or perhaps eccentricity if you want to call it that way) of this formula is as follows:
1. The two criteria are combined by using the multiplication operator (which is "*") which functions like the "AND" condition where you tell Excel that both arguments MUST be met to return the result.
2. It is the IF function that actually enables this formula to have the 'array' flavor because what it basically does is to compare individually IF each cell on range E2:E1000 is the same as cell A10, AND AT THE SAME TIME comparing each cell on range D2:D1000 is the same as cell B3. If both IFs match, please sum all the data up. That's what you want Excel to do.
3. Beware, though, that you have to press CSE combo button (Control+Shift+Enter) for this array formula to work so that the curly bracket {} shows automatically which marks it as an array. Hence, please do NOT type the curly bracket yourself in the Formula Bar.
4. The shortcoming of using an array formula is that you can't copy paste downwards easily like you're used to doing very quickly. Excel will display an error message saying "You cannot change part of an array" - seemingly due to the fact that Excel doesn't even vary the semiabsolute cell addresses that we intentionally use in the formula so they could be carried forward when doing copy & paste. This means you'll have to paste (Ctrl+V) it one at a time throughout the working range. Luckily, you can still copy by row and paste it downwards rather than one for each cell!
In any case, this array combination formula nicely does the job of SUMIFS equally well although you have to put up with its few weirdos. Not bad, really not bad for an alternative as well as a good chance learning about array. (EJ - 19/5/11)
Wednesday, March 10, 2010
Show Desktop Alert for filtered messages
When you have set all your messages filtered properly, another problem crops up. They get directed covertly straight to the selected folders without showing the snazzy Desktop Alert that you'd usually see when a new mail arrives (because the Desktop Alert by default only works for the Inbox folder!).
Wednesday, September 24, 2008
Dealing with AutoFilter
EXCEL TIPS: AutoFilter in a Messy Data Table
Work situation:
You have just exported a large amount of data into Excel or converted a .txt file into an .xls file. The data automatically sits in a table-like manner on your spreadsheet but it looks disorganised. You want to manipulate the data table using AutoFilter to speed up removing the blank rows that clutter around and through the entire range of the table.
Problem:
However, when you activate the AutoFilter & click the drop-down arrows of the column headings, they only display the basic criteria (Sort Ascending/Descending, All, Top 10, Custom) but where is the data? This is due to the blank rows between the headers and the first row of data.
Solution:
Turn off AutoFilter first. In order for Excel to treat your whole range of data as being one single table, highlight the entire table range. And turn the AutoFilter back on. Done! Now each column heading should show all the data underneath it. (EJ)
