Solved

Another excel formula request but on a range

Posted on 2014-09-30
21
66 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 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

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

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

820 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