For OnPrem VMs, with specific vCPU and RAM, the right VM sizes often have to be found in Azure. It is important that the prices are as low as possible. For example, with Windows, a machine with 8 vCPU and 32GB RAM is the cheapest Azure variant if the machine requires at least 5 vCPU and 5GB RAM. In addition, it is often not just one machine that is searched for, but a whole list of machines.
If there is only a total number of vCPU and RAM, the Solution with the Solver can be used. If only one machine is required, you can look up and filter under https://azureprice.net/. However, if several VMs are to be analyzed quickly, the site’s API must be purchased or a script must be created that queries Microsoft for prices. But a query becomes difficult in Excel because two criteria have to be searched for. However, both criteria must not only be checked for equality, but also for any larger values for both criteria if the price falls as a result.
However, a list in Excel should serve as a solution, which determines the appropriate size. Excel has suitable functions for this as well and is very well suited for processing list data. The list with the prices is simply exported from https://azureprice.net/ and imported into Excel. An
index column must be added that assigns a unique number to each row in the table, making it easier to reference additional information later. The data source now looks like this:
The information on the VMs you are looking for is entered in the
vms table (yellow fields). The OS (
L for Linux and
W for Windows), the vCPU and RAM are given in GB for each VM. The remaining columns are filled by the formulas.
The minimum price (column
D) is determined using the
MINIFS function. The price list does not have to be sorted because the entire column is searched.
MINIFS first argument is the column in which thelowest price should be found (line 2). Since this information depends on the operating system,
IF is used here. The other search criteria are in the following lines. Line 3 shows that the number of vCPU must be greater than or equal to the
numberOfCores column and line 4 that the number of RAM must be greater than or equal to the
=MINIFS( IF([@OS]="L";prices[linuxPrice];prices[windowsPrice]); prices[numberOfCores];">="&[@vCPU]; prices[memoryInGB];">="&[@[RAM (GB)]] )
Almost the same function is used in column
E, except that it also filters the price just determined in order to find the index in the price table. With this index it is now simple to look up the VM name, CPU and RAM to generate the output in columns
Not Working Alternatives
Excel offers many other functions for looking up and searching in lists. However, these commonly used methods did not work in this complex search for the next largest criteria with a minimum price.
VLOOKUP: the list must be sortet and two search criteria can only be used with
CHOOSE, whereby only equals checks are possible
SOLVER: the same solution as in the other blog post, but
SOLVERshould try differend
VLOOKUPs to find the lowes price. Unfortunately
SOLVERcan not evaulate the results of
DMIN: will work, but needs the column names above the search criteria
INDEX: can be used with multiple criteria to find the next bigger element in a list. But the search does not work correct and the list needs to be sorted.