Solved

Another excel formula request but on a range

Posted on 2014-09-30
21
56 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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:teylyn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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:teylyn
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
^teylyn nailed it.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
Comment Utility
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:teylyn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Is there a way that the #N/A! can be ?
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
yes
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
or

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

Expert Comment

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

772 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

11 Experts available now in Live!

Get 1:1 Help Now