Solved

Another excel formula request but on a range

Posted on 2014-09-30
21
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 4
  • +1
21 Comments
 
LVL 26

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 26

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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
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 26

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
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
 
LVL 27

Expert Comment

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

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
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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 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