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
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?
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!)
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: I'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).
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.
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")
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")
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"
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).
Oh, I agree. You latest formula is more-encompassing: not just values less than 10, but also non-numeric or blank.
0
Featured Post
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.