Blog of Calc LLC

Excel Solver is used to solve optimization problems, which is like solving equations. In this tutorial, we will give a very simple example on how to use Excel Solver. Basic example Suppose your car is low on gasoline. The gasoline is $3/gallon. You have a budget of $50. How much gasoline can you buy? Of course, for a problem this simple, we do not even need a Solver to solve the problem. However, the purpose is to teach the users how to use Excel Solver, so we make the problem extremely simple. We have the following framework: Goal: maximize quantity of gasoline Relationship: cost=quantity * unit price Constraint: cost<=budget 1. In cell B1, we enter unit price "3", in cell B4, we enter the budget 50. In cell B2, we just enter an arbitrary number as the number of gallons. We entered 5. In cell B3, we enter the formula "=B1*B2" to let it be the cost, which equals to 15. 2. Click Solver under the Data menu. 3. A Solver dialog window pops up. In "Set objective...

Suppose you have a column of numbers and the data can be positive or negative. How do you count the number of times the data changes from positive to negative, or vice versa? We will use three functions, SIGN , INT and SUM to solve the problem. First we add a helper column B, name it "sign", then use SIGN function to get its sign. If the data is positive, it returns 1. If the value is negative, it returns -1. The formula is like: =SIGN(A2) Then we add another helper column C, call it "sign change", which checks whether sign of the current value is different from the last value. INT function is used to return the logical value TRUE or FALSE into 1 or 0. At last, at the end of column C, we use a SUM function to sum all the value changes. In this example, the data sign changes for 4 times (-1 to -3, -3 to 4, 2 to -5, and -5 to 6). VBA method Below is the VBA code to achieve it. You need to add a clickable button on the spreadsheet and assign the following code t...

Excel has a very useful function convert numbers from one measure unit to another, for example, from Fahrenheit to Celsius, or from mile to kilometer. The function is  CONVERT . For example, if we want to convert temperature 1.2 mile to kilometer, how to do it? One way is using the equation: 1 mile = 1.60934 kilometer If you do not know the conversion equation, you can just use the CONVERT function. Since kilometer is not a basic unit in the CONVERT function, you need to convert mile into meter first and then to kilometer. For example: =CONVERT(1.2,"mi","m") will turn mile into meters. If you divided the above number by 1000, you will get kilometer. =CONVERT(1.2,"mi","m")/1000 See the following figure for example. You can also convert kilometer into mile in a similar way. You first need to convert kilometer into meter, then use the CONVERT function. The formula is: =CONVERT(2.35*1000,"m","mi")

Excel has a very useful function convert numbers from one measure unit to another, for example, from Fahrenheit to Celsius, or from mile to kilometer. The function is CONVERT . For example, if we want to convert temperature 76 F to Celsius, how to do it? One way is using the equation: C=(F-32)/1.8 If you do not know the conversion equation, you can just use the CONVERT function. For example: =CONVERT(76,"F","C") In the following figure, both methods give the same result.

YEARFRAC function returns the number of fractional years between two days. Syntax YEARFRAC( start_date, end_date, [day_count_convention] ) The day_count_convention is optional and will be ignored in this tutorial. Examples The above result shows that there is 0.497 year between 1/1/2020 and 6/30/2020, or the duration between 1/1/2020 and 6/30/2020 is 49.72%.

YEAR function returns the year part of a date. For example, the year part of 2/20/2020 is 2020. Syntax YEAR(date) Examples

The WORKDAY  function answers questions like this: what date is it after 15 work days from 1/3/2020? WORKDAY function assumes that Saturday and Sunday are not work days. However, in some countries, the rest days may be Sunday and Monday. WORKDAY.INTL solve this problem. Syntax WORKDAY( date, number of work days, [weekend], [holidays] ) weekend is optional. weekend is a string of seven 0s and 1s, representing seven days from Monday to Sunday. 0 indicates work days and 1 indicates rest days. The default is "0000011", which means Monday to Friday are work days, while Saturday and Sunday are not weekend. If Sunday and Monday are rest days while Tuesday to Saturday are work days, we should use "1000001". Holidays  are optional If  number of work  days is negative, we calculate like what date it is 3 work days before this date Examples In the above example, it shows that 25 work days after 1/2/2020 is 2/13/2020 in China.

WORKDAY function answers questions like this: what date is it after 15 work days from 1/3/2020? Syntax WORKDAY( date, number of work days, [holidays] ) Holidays are optional If number of work days is negative, we calculate like what date it is 3 work days before this date Examples In the above example, it shows that 15 work days after 1/3/2020 is 1/24/2020. However, if we consider holidays, 15 work days after 1/3/2020 is 1/27/2020, because of Martin Luther King Day.

WEEKNUM  function returns the number of week of a date, that is, whether a date is the 5th, or 17th week of a year etc. Syntax WEEKNUM(date, [ type ]) type is optional.  If type is 1, it means the week starts on Sunday. The default value of  type  is 1. If type is 2, it means the week starts on Monday.  If  type  is 21, WEEKUM is equivalent to ISOWEEKNUM type  can be other values, which are rarely used. See the official reference if you need to use it. Example

WEEKDAY function tells whether a day is the 2nd, 4th day of the week etc. Syntax WEEKDAY(date, [ type ]) type is optional. If type is 1, Sunday is the 1st day and Saturday is the 7th day. If type is 2, Monday is the 1st day and Sunday is the 7th day. If type is 3, the value for Monday is 0 and the value for Sunday is 6. The default value for type is 1. Examples

TIMEVALUE function returns the percentage of time has pass for a day. For example, at 12:00 PM, 50% of the time of a day (24 hours) has passed. At 18:00 PM, 75% of the time of the day has passed. However, TIMEVALUE function does not return percentage directly. It returns a value between 0 and 1 such as 0.05 or 0.42. You can format it as percentage though. Syntax TIMEVALUE(time) Examples

TODAY function returns the current date. Syntax TODAY() Examples Notes TODAY function is a volatile function. The value it returns is the date when the spreadsheet is recalculated, not the date when the spread is created. Today only returns the current date without a time component. To create a time with date and time, use NOW function.

TIME function converts hour, minute, and second into a time. Syntax TIME(hour, minute, second) Examples

SECOND function returns the seconds part of a time. For example, the seconds part of 8:21:14 is 14. Syntax SECOND(time) Examples

NOW function returns the current date and time. Syntax Now() Examples Notes NOW is a volatile function. Each time the spreadsheet is recalculated, the result of NOW function will be updated. If you do not need the current time but only current date, use TODAY function instead.

NETWORKDAYS.INTL function calculate the net work days between two days for countries outside of US. Syntax NETWORKDAYS.INTL(start date, end date, weekend indicator, [holidays]) Weekend indicator is a string of seven 0s or 1s indicating which days of a week are weekends. 0 indicates work days and 1 indicates weekends. The first number indicates Monday. For example, "0000011" indicates Saturday and Sunday are weekends, while "1000001" indicates Monday and Sunday are weekends. Examples Notes You can use an integer 1 to 17 as the weekend indicator. However, that is not as visual as "0000011".

NETWORKDAYS function does NOT calculate "network days" such as the number of days your computer network has been on. It calculates "net work days" -- the number of days between two dates except weekend or holidays. Syntax NETWORKDAYS(start date, end date, [holidays]) [holidays] are optional. Examples Notes You need to provide holidays by yourself. If you do not do it, all holidays will be regarded as work days.

MONTH function returns the month part of a date. For example, the month part of 2/15/2020 is 2. Syntax MONTH(date) Examples Notes You can use any format for the dates, as long as they are valid dates. The MONTH function will return an error if the argument is not a valid date, such as "15/2/2019"

MINUTE function returns the minute component of a time. Syntax MINUTE(time) Examples Notes If you have already used the 24-hour system such as "17:48:21", you should not add "PM" after it. "17:48:21 PM" is not regarded as a time but a general text. MINUTE("17:48:21 PM")  gives a "#VALUE!" error.

ISOWEEKNUM returns the number of week of a date using ISO standard, that is, whether a date is the 5th, or 17th week of a year etc. Syntax ISOWEEKNUM(date) Example Notes Per ISO standard, a week begins on Monday and ends on Sunday. Under ISO standard, whether a week belongs to a years depending whether its Thursday falls in a year. Therefore, January 1 is not always week 1 of a year, it can be week 53 of the last year. Similarly, December 31 is not always week 52, it can week 53 of the current year, of week 1 of the next year.