Solved

Another excel formula request but on a range

Posted on 2014-09-30
21
62 Views
Last Modified: 2014-10-27
I have another formula request in excel but this time it is with a range and not sure how to do that

Dealing with Columns E & G and want to set Column O based on the values there
 
If columns E & G are < 1,000,000 and > 10 then O should be A
NEXT for all the others if G is greater than E use G else use E to determine
If columns E or G are < 5,000,000 and > 999,999 then O should be B
If columns E or G are < 25,000,000 and > 5,000,000 then O should be C
If columns E or G are < 50,000,000 and > 25,000,000 then O should be D
If columns E or G are > 50,000,000 then O should be E
0
Comment
Question by:Matt Pinkston
  • 9
  • 4
  • 4
  • +1
21 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40353523
just to give you heads up. I am working on this .  so nobody else re invent the wheel
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353548
There is an issue with your logic:  you are testing exclusively with less-than and greater-than and so you are excluding the possibility of values equalling your criteria.  This is most obvious in your first test (E/G < 1,000,000) and your second sub-test (E/G > 999,999).  Please clarify this.

Additionally, what should the result be if E/G are less than 10?

-Glenn
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40353553
assuming your data starts from second row  .  you can put the following formula in the column O and drag down

=IF(AND(E2<1000000,E2>10,G2>1000000,G2>10),"A",IF(G2>E2,G2,IF(E2>G2,E2,IF(OR(AND(E2<5000000,E2>999999),AND(G2<5000000,G2>999999)),"B",IF(OR(AND(E2<25000000,E2>5000000),AND(G2<25000000,G2>5000000)),"C",IF(OR(AND(E2<50000000,E2>25000000),AND(G2<50000000,G2>25000000)),"D",IF(OR(E2>50000000,G2>50000000),"E","")))))))
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353567
I have an alternate solution that I believe is simpler and easier to update/edit.

Create a lookup table on a separate sheet like so:
sample lookupI've added a possible row for any result where E/G is less than 10 - "X"

Then, add this formula in column N and copy down:
=VLOOKUP(MAX(E2,G2),Sheet2!$A$1:$B$7,2,TRUE)
(Replace "Sheet2!" with the sheet name where the lookup table resides).

See the attached workbook for an example.

Regards,
-Glenn
EE-Q-28528757.xlsx
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40353571
How about this in O1

=LOOKUP(MAX(E1,G1),{10,1000000,5000000,25000000,50000000},A1:E1)

copy down as required.

If both E1 and G1 are less than 10, the formula returns an error, since that option is not in your logic.

cheers, teylyn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353579
ProfessorJimJam, I'm only getting numbers as results.

teylyn, I'm only getting zero (0) as results.  I think the questioner want to return "A" - "E" instead of the values in those columns.  But I like the lookup idea...maybe a CHOOSE will work with this.
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40353593
thanks Glenn.

here it is fixed now.

=IF(AND(E2<1000000,E2>10,G2<1000000,G2>10),"A",IF(G2>E2,G2,IF(E2>G2,E2,IF(OR(AND(E2<5000000,E2>999999),AND(G2<5000000,G2>999999)),"B",IF(OR(AND(E2<25000000,E2>5000000),AND(G2<25000000,G2>5000000)),"C",IF(OR(AND(E2<50000000,E2>25000000),AND(G2<50000000,G2>25000000)),"D",IF(OR(E2>50000000,G2>50000000),"E","")))))))
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353594
How about this...borrowing from teylyn's idea.  No need for a lookup table either:
=CHOOSE(MATCH(MAX(E2,G2),{10,1000000,5000000,25000000,50000000},1),"A","B","C","D","E")

-Glenn
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40353598
shot.png
I read the question as the values in columns A to E, but maybe not. A 0 as a result means you don't have the setup  right.

If the text "A" to "E" needs to be returned then

=LOOKUP(MAX(E1,G1),{10,1000000,5000000,25000000,50000000},{"A","B","C","D","E"})
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353599
ProfessorJimJam, still numbers.  The issue seems to be in the bolded section:

=IF(AND(E2<1000000,E2>10,G2<1000000,G2>10),"A",IF(G2>E2,G2,IF(E2>G2,E2,IF(OR(AND(E2<5000000,E2>999999),AND(G2<5000000,G2>999999)),"B",IF(OR(AND(E2<25000000,E2>5000000),AND(G2<25000000,G2>5000000)),"C",IF(OR(AND(E2<50000000,E2>25000000),AND(G2<50000000,G2>25000000)),"D",IF(OR(E2>50000000,G2>50000000),"E","")))))))
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353601
^teylyn nailed it.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40353617
Glenn,

you may appreciate that the asked question is vague and is not clean.  you pointed right on the bold section of code, but that is exaxtly what Pink asked  "NEXT for all the others if G is greater than E use G else use E to determine"   i do not know how perceive this  .


anyways lets see what Pink has to say.  

by the way  .

i guess you nailed it with =CHOOSE(MATCH(MAX(E2,G2),{10,1000000,5000000,25000000,50000000},1),"A","B","C","D","E")
0
 

Author Comment

by:Matt Pinkston
ID: 40353635
I tried

=IF(AND(E2<1000000,E2>10,G2<1000000,G2>10),"A",IF(G2>E2,G2,IF(E2>G2,E2,IF(OR(AND(E2<5000000,E2>999999),AND(G2<5000000,G2>999999)),"B",IF(OR(AND(E2<25000000,E2>5000000),AND(G2<25000000,G2>5000000)),"C",IF(OR(AND(E2<50000000,E2>25000000),AND(G2<50000000,G2>25000000)),"D",IF(OR(E2>50000000,G2>50000000),"E","")))))))

and got 1E+06 when Column E was 1,000,000 and Column G was blank
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40353653
pinkstomp, can you help us clarify what you really need? The phrasing of the question is a bit ambiguous.

You wrote:
If columns E & G are < 1,000,000 and > 10 then O should be A  is that column A or the text A
NEXT for all the others if G is greater than E use G else use E to determine is this the highest value of E and G, like
NEXT for all the others if G is greater than E use G to determine (else use E to determine)
If columns E or G are < 5,000,000 and > 999,999 then O should be B is this from 1,000,000 to 4,999,999
If columns E or G are < 25,000,000 and > 5,000,000 then O should be C
If columns E or G are < 50,000,000 and > 25,000,000 then O should be D
If columns E or G are > 50,000,000 then O should be E is this column E or the text "E"

When you refer to column E do you mean the cell in column E in the current row?
0
 

Author Comment

by:Matt Pinkston
ID: 40353668
Let me try and make it clearer

If columns E & G are < 1,000,000 and > 10 then Column O should be "A"

NEXT for all the next portion of the logic if G is greater than E use G otherwise use E as the comparator value
 
 If (higher of E or G) is < 5,000,000 and > 999,999 then O should be "B"
 If (higher of E or G) is < 25,000,000 and > 5,000,000 then O should be "C"
 If (higher of E or G) is < 50,000,000 and > 25,000,000 then O should be "D"
 If (higher of E or G) is > 50,000,000 then O should be "E"
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353720
Okay, it looks like teylyn's solution is the simplest and should produce the results you need (assuming data starts on row 2):
=LOOKUP(MAX(E2,G2),{10,1000000,5000000,25000000,50000000},{"A","B","C","D","E"})

The only condition that you need to clarify is:
What result, if any, do you want if E & G are less than 10?  (this formula will return #N/A! if this is the case).

-Glenn
0
 

Author Comment

by:Matt Pinkston
ID: 40353725
Is there a way that the #N/A! can be ?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353733
Do you mean, "Is there a way that the #N/A! can be a question mark?" :-)

If so, then "yes" it can be modified:
=LOOKUP(MAX(E2,G2),{0,10,1000000,5000000,25000000,50000000},{"?","A","B","C","D","E"})
0
 

Author Comment

by:Matt Pinkston
ID: 40353734
yes
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40353752
or

=iferror(LOOKUP(MAX(E2,G2),{10,1000000,5000000,25000000,50000000},{"A","B","C","D","E"}),"?")
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353817
Oh, I agree.   You latest formula is more-encompassing:  not just values less than 10, but also non-numeric or blank.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now