Hello,
I have a large list of tenants down the page. Across the page I have 12 months of numbers related to their square footage. The Mode Function by itself works nicely when I am building out a table off of a data extract. The issue is how can I be certain the formula is picking up the right range? How can I make it more dynamic where it says Search this tenant on this data set. When you find it do a MODE on this range of cells?
Regards,
Anthony
Sorry. This is my current formula
=MODE(JDESqFtg!J2:U2)
Very basic. Should I do an If statement with a vlookup and if it is found do a MODE?
Maybe something like this...
Names in the range JDESqFtg!A2:A6
Numeric data in the range JDESqFtg!J2:U6
On some other sheet:
A2 = some name
This formula entered in B2:
=MODE(INDEX(JDESqFtg!J2:U6,MATCH(A2,JDESqFtg!A2:A6,0),0))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Works like a champ! Maybe Mode isn't the correct function to use? I have 12 months of a tenants square footage. If the tenant is vacant 9 months that is 9 zeros vs 3 months of square footage. I need the formula to do the match like you stated but I need it to look at 12 months and pull the appropriate square footage for that unit. I attached file as a reference. Look at the highlighted yellow row on both tabs. I need Romanos to pull 923. I almost need it to disregard zeros in the mode and do the most frequent number in the amounts greater than zero?
Ok, there's a slight problem...
There are 2 instances of ROMANO'S JEWELERS. One on row 138 and the other on row 176 (which you have highlighted).
The formula will find the first instance on row 138.
Both instances have the same 2 numbers, 0 and 923, but not exactly the same count or sequence.
So, which instance do you want to use for the calculation?
Both. One is a lease ending on 3/31/2012 and the other is a lease start of 4/1/12 through 3/31/2015. It is the same tenant and space but it is a renewal with different dates. So you see the square footage on the first one (row 138) only through March and then you see the square footage on the second one (row 176) start on April through December.
Try one of these this array formulas**:
=MODE(IF(JDESqFtg!D$2:D$201=B183,JDESqFtg!J$2:U$201))
If you want to exclude zero values:
=MODE(IF(JDESqFtg!D$2:D$201=B183,IF(JDESqFtg!J$2:U$201>0,JDESqFtg!J$2:U$201)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
I believe that gets me the result I need. The formula excluding zeros. Thanks!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks