[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 76
  • Last Modified:

Another excel formula request but on a range

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
Matt Pinkston
Asked:
Matt Pinkston
  • 9
  • 4
  • 4
  • +1
1 Solution
 
ProfessorJimJamCommented:
just to give you heads up. I am working on this .  so nobody else re invent the wheel
0
 
Glenn RayExcel VBA DeveloperCommented:
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
 
ProfessorJimJamCommented:
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Glenn RayExcel VBA DeveloperCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
ProfessorJimJamCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
^teylyn nailed it.
0
 
ProfessorJimJamCommented:
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
 
Matt PinkstonAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Matt PinkstonAuthor Commented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Matt PinkstonAuthor Commented:
Is there a way that the #N/A! can be ?
0
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Matt PinkstonAuthor Commented:
yes
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
or

=iferror(LOOKUP(MAX(E2,G2),{10,1000000,5000000,25000000,50000000},{"A","B","C","D","E"}),"?")
0
 
Glenn RayExcel VBA DeveloperCommented:
Oh, I agree.   You latest formula is more-encompassing:  not just values less than 10, but also non-numeric or blank.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 9
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now