Hi
Many times you got stuck with a thought of why Minif and Maxif is not provided along with Sumif, Countif ? And yes, even I do think on the same lines. Today I am going to demonstrate, how you can easily achieve the same though not provided by excel, but I still expect Microsoft to add this support natively.
We can achieve this with use of array formulas and this is quite simple to understand so don’t get worried by the name of array formula.
For those you haven’t yet heard the name of array formulas, these are some special formulas where you need to hit ‘ctrl+shift+enter’ together instead of enter in standard formulas
We are going to use below table (Considering it in range A1:D6)
|
A |
B |
C |
D |
| 1 |
Batch No. |
Item No. |
Quantity |
Rate |
| 2 |
789 |
123 |
100 |
10 |
| 3 |
789 |
456 |
450 |
12 |
| 4 |
546 |
753 |
245 |
4.5 |
| 5 |
546 |
456 |
100 |
9.97 |
| 6 |
546 |
456 |
210 |
9.96 |
Situation 1
Find max rate for item no. 456 (Criteria in A10, result in B10) (Max with One Condition)
=Max(if(B2:B6=A10,D2:D6,”False”)) Hit “ctrl+shift+enter”
You will get the answer 12
Situation 2
Find min rate for item no. 456 (Criteria in A12, result in B12) (Min with One Condition)
=Min(if(B2:B6=A12,D2:D6,”False”)) Hit “ctrl+shift+enter”
You will get the answer 9.96
Situation 3
Find max rate for batch no.546 and item no. 456 (Criteria in A14 & B14, result in C14) (Max with Two Condition)
=Max(if(A2:A6=A14,if(B2:B6=B14,D2:D6,”False”),”False”)) Hit “ctrl+shift+enter”
You will get the answer 9.97
Hope you will be able to save your valuable time by making use of these formulas
Send your feedback and questions by comments
January 16, 2012
Hi Friends
After many appreciations on Part I and Part II, there were few queries which I am trying to address in this part. The purpose of this series is to make everyone aware about text fuctions and advanced use of them.
Previously we seen the use of left, right & find formulas. In this part we will additionally see use of substitute.
Situaltion
As I mentioned, reason for this part is few of the queries. In Part I we have seen how to split first name and last name, where as in second part we seen how we can also make a split between first name, middle name and last name, a query has been raised about can we also split if the name is like ‘Carl Van C. Chrome’.
Now if you see ‘Carl Van C. Chrome’, this name is also made of 3 parts like first name ‘Carl Van’, middle name ‘C.’ and last name ‘Chrome’ but the spaces appearing here are 4. we are going to see below how to achieve this, plus to add a little more complexity, I am further going to assume that the string might also contain names with space in first name or no space in first name (normal)
Like previous parts in this example as well we are going to consider that the above string ‘Carl Van C. Chrome’ is in cell A3 and we want to separate this in 3 parts
First Name
The most complicated part is finding first name even though it contains a space, logically what we want to achieve is ignore the space in first name, but before that we need to be sure whether the first name has the space.This can be determined with the number of spaces the string contains
If there are 2 spaces it’s a normal first name and if there are 3 spaces it contains space in first name
Below formula can be used to find the number of spaces in a string or actually any specific character in a string :
=LEN(A3)-LEN(SUBSTITUTE(A3,” “,”"))
What we have done is replaced the character to be searched for with nothing and calculated the difference in length
If the length is 3 below formula can be used to find the first name:
=LEFT(A3,FIND(” “,SUBSTITUTE(A3,” “,”",1),1))
Here the logic is to replace the first occurrence of space and get the first name as we found in Part II.Next using if condition we can use different formulas for 2 different situations as below
=LEFT(A3,FIND(” “,IF((LEN(A3)-LEN(SUBSTITUTE(A3,” “,”")))=3,SUBSTITUTE(A3,” “,”",1),A3),1))
Middle Name
Similar to above first name even in middle name could have 2 situations which needs to be solved, you can make use of below formula, the logic here is similar to what we have used in Part II
=MID(IF((LEN(A3)-LEN(SUBSTITUTE(A3,” “,”")))=3,SUBSTITUTE(A3,” “,”",1),A3),(FIND(” “,(IF((LEN(A3)-LEN(SUBSTITUTE(A3,” “,”")))=3,SUBSTITUTE(A3,” “,”",1),A3)),1))+1,(FIND(” “,(IF((LEN(A3)-LEN(SUBSTITUTE(A3,” “,”")))=3,SUBSTITUTE(A3,” “,”",1),A3)),A8+1))-(FIND(” “,(IF((LEN(A3)-LEN(SUBSTITUTE(A3,” “,”")))=3,SUBSTITUTE(A3,” “,”",1),A3)),1))-1)
I know it is long enough to get confused but so see the highlighted section which has been used from first formula
Last Name
To find last name as well we are going to make use of same logic as was been in part II, below formula can be used
=RIGHT(A3,LEN(A3)-FIND(” “,(IF((LEN(A3)-LEN(SUBSTITUTE(A3,” “,”")))=3,SUBSTITUTE(A3,” “,”",1),A3)),(FIND(” “,(IF((LEN(A3)-LEN(SUBSTITUTE(A3,” “,”")))=3,SUBSTITUTE(A3,” “,”",1),A3)),1))+1))
here again same
This is little complex, still tried to explain here best of my knowledge, if you still feel this is confusing go ahead and create some helper columns for different formulae used again and again to avoid confusion
Let me know your feedback and comments also queries if you have any
January 1, 2012
Friends, have you came across a situation when you want to calculate inches from centimeters or hours from days in excel, very simple we do the mathematical calculation easy right? but there is more easier way provided by excel CONVERT function
Function convert is one of the very useful function but also rarely used. This function is ultra easy to use.
Syntax – =CONVERT(number, from_unit, to_unit)
There is specific set of text provided which can be used in from unit and to unit, also we can use cell references which contains these specific texts. E.g. to convert 21 days to hours formula would be =CONVERT(21,”day”,”hr”)
Giving below list of useful texts that can be used as units:
| Gram |
g |
| Kilogram |
kg |
| Slug |
sg |
| Pound mass |
lbm |
| Meter |
m |
| Feet |
f |
|
| Year |
yr |
| Day |
day |
| Minute |
mn |
| Second |
sec |
| Inch |
in |
| Yard |
yd |
|
| Liter |
l |
| Gallon |
gal |
| Horsepower |
HP |
| Watt |
W |
| Celsius |
C |
| Fahrenheit |
F |
|
Also see below some examples about usage of this function
- Convert 2 kilograms to pounds =CONVERT(2, “kg”, “lbm”)
- Convert 5 feet to meters =CONVERT(5,”ft”,”m”)
- Convert 10 meters to yards =CONVERT(10,”m”,”yd”)
- Convert 49 Celsius to Fahrenheit = CONVERT(49,”C”,”F”)
Please write to me if you find this post useful
September 11, 2011
Hi friends,
Welcome to the new site hope you will enjoy new features and customizations that would be coming soon.
update — Sep 5, 2011 11:23 PM
Entire site with all posts comments and pages has been successfully moved and now available online.
We have added dozens of new plug-ins like facebook connect,twitter connect, single sign on and facebook fan box.
September 4, 2011
In one of the comments it has been asked as query about splitting some text from a string. In this post I am answering the query plus further extending it so that we can cover maximum use of Text Formulas Left, Right and Mid.
The query -
How to get Ch. No. 123456 from entire string like
Case 1 – ‘Ch.No. 123456 being the amt paid to party against bill no.256 dt. 12.07.2011′ or
Case 2 – ‘Mr. X given Ch.No. 123456 being the amt paid to party against bill no.256 dt. 12.07.2011′.
Solution -
Ch. No. is always of 6 digits and assuming this, there are 2 ways to achieve this as described below
(In examples complete string has been entered in C5)
Option 1 – This can be used for Case 1, where Ch. No. is in beginning followed by numeric ch. no.
Formula - =LEFT(C5,13)
In case you want only numeric ch. no. then you can use below formula
Formula - =MID(C5,8,6)
Option 2 – This can be used in both cases, whether the ch. no. is in beginning or not
Formula - =MID(F2,FIND(“Ch.No.”,F2),13)
In case you want only numeric ch.no. then you can use below formula
Formula - =MID(C5,FIND(“Ch.No.”,C5)+7,6)
Do let me know if you find this post useful or if you have any more queries on excel
August 28, 2011
Hi all,
Writing after many days……
Yesterday I came across a situation where I wanted to have cube root. Now for square root there is a function that we are used to =SQRT(), but there is no function but there is a nice way to achieve this with a special character ‘^’.
Now I know that this is the same character we use for calculating nth power of any value but there is a very simple arithmetic and you can even calculate root as well. Basically for calculating root you need to mention it as 1 by that power.
Example: If i want to calculate Square root of 4, it would be 4^(1/2). Don’t forget the brackets they are very important. Similarly for cube root the power would be (1/3) so to find cube root of 8 you need to give formula as 8^(1/3).
This way you can go till whatever power you want in roots.
Hope this will help some of you who are not aware of this.
March 5, 2011
excel, function, formula, tips, tricks, ms excel, large, small, info
Continue June 8, 2009
Hi friends,
Here goes the Text Split formula Part II, which will explain how to split First Name, Middle Name and Last Name.
Of course, you can use this many other ways too, so try to understand the logic behind this.
Formulas Used:
Just like previous part, we are also going to use Left, Right & find formulas.
All the three formulas are much simple to use and the real magic is binding them together to get desired results.
LOGIC:
In the first part, we had used find to determine the location of space between two words and then we split it with left & right.
Now in this part we are going to find out further how to find out second space in the given string.
Situation:
If you have a string ‘Firstname Middlename Lastname’, in A3 how you will separate this text with formulas so that you will have firstname, middlename and lastname in separate columns.
Firstname:
Very easy just like what we did in the last part find out the place of separator and use left function.
The formula for the given example is…=LEFT(A3,FIND(” “,A3,1))
Middlename:
This is most interesting part that everybody wants. Here you need to use all the three functions together. First you find out 2nd location of separator and using left formula you will get string ‘firstname middlename’. Then using right formula as in part one you can separate middlename. As this is quiet complicated I will explain this step by step and then together.
Step 1: Find 2nd location of separator formula is … =FIND(” “,A3,FIND(” “,A3,1)+1)
Note: remember to add ’1′ to last separator location to find out next, otherwise it will keep giving you same results.
Step 2: Find string ‘firstname middlename’ formula is … =LEFT(A3,FIND(” “,A3,FIND(” “,A3,1)+1))
Step 3: Use ‘Right’ function to separate middlename from string in step 2, formula is…=RIGHT(LEFT(A3,FIND(” “,A3,FIND(” “,A3,1)+1)),FIND(” “,A3,1))
Lastname:
Comparatively this is easy, and similar to what we did in last part the only difference is instead of 1st location of separator we are going to use last location of separator.
The formula for the given example is… =RIGHT(A3,LEN(A3)-FIND(” “,A3,FIND(” “,A3,1)+1))
Conclusion:
This way you can split ‘n’ number words with any given separator. Try to find out way to use string functions differently it has enormous power.
Please add your feedback and comments below.
December 22, 2008
It always happens that you need to use text to column feature in excel. for some it’s rare but from some it’s everyday exercise. Think about the advantages that you will get if you can split text with formulas.
Let’s see how you can do that with example:
If you have a string ‘Firstname Lastname’, in A3 how you will separate this text with formulas so that you will have firstname and lastname in separate columns.
There are two formulas involved.
Left and right
How to Use left formula for fisrtname:
the main factor in this formula is finding out the string length of the firstname as it will vary. The solution is find out the place of separator (space in given example)
The formula for given example will be… =left(A3,find(” “,A3,1))
Now the difficult part of it that is the right formula use for lastname:
here as well the main factor is finding out the length of the string lastname as it will also vary everytime. so to find out the length we will first find out the place of separator (space in given example) and reduce it from the total length.
The formula for given example will be… =right(A3,len(A3)-find(” “,A3,1))
In next part we will see how can we use the formulas to split text “Firstname Middlename Lastname”.
July 13, 2008
This type of formatting of table is easily possible with Excel 2007, but before that it was a pain.
Let’s see how we can use conditional formatting to our advantage in this area.
‘Zebra Stripes’ is basically coloring your table’s each alternate row, this will increase the readability of the table.
Let’s see how to achieve this in step by step mode.
1. Select the entire table.
2. Goto Format – Conditional formatting
3. Select ‘Formula Is’ ption in 1st Dropdown
4. Enter Formula =Mod(Row(),2)=0
5. Select the color you want to fill every alternate row and click ok
The table you selected will get filled with White and other color you selected,
If you want it to be filled with other color than white You can also use following additional conditioning.
6. Goto Format – Conditional formatting
7. Select ‘Formula Is’ ption in 1st Dropdown
8. Enter Formula =Mod(Row(),2)=1
9. Select the color you want to fill every alternate row instead of white and click ok
You can use this same way for columns as well with Formula Column() instead of Row() above.
Hope you find this useful and use it in your daily works.
Please comment me your feedbacks and suggestions you can also mail me your excel queries.
November 11, 2007
Previous page
