Report

Trying to create a multi-column formula. [Solved]

Ask a question oduana777 7Posts Thursday January 12, 2017Registration date March 23, 2017 Last seen - Last answered on Mar 23, 2017 at 07:57 PM by oduana777
Hello,

I need help making a formula. I will do my best to describe.

The letters (A-I) refer to separate columns.

A-B=C to be divided by D the result is column E. also
A-F=G to be divided by H the result is column I.
Sometimes A-B or A-F will equal 0 which can not be divided by D or H. When that happens I
want C and G to to automatically equal .01 instead of zero so that i can still have a scale of numeric values negative or positive.

After all this is done I want Excel to compare the results of E and I and give me the lowest value between the two (if there are 2) or just the one value that exists as column J.

When either columns B & F are blank there is no need to continue solving for that portion of the formula. I just want it to move on to the next portion.



A-B=C/D=E
A-F=G/H=I
E and I compared, if both have a value the lowest is selected to become J (final score). If only one of the two E or I exists I want that sole value reflected in column J.

I need the formula to be spatially correct as I am still a beginner to Excel.

Thank You so much for your help!
See more 
Helpful
+0
plus moins
Hi Oduana777,

Here we go:
C2: =IF((A2-B2)=0,0.01,(A2-B2))
E2: =C2/D2
G2: =IF((A2-F2)=0,0.01,(A2-F2))
I2: =G2/H2
J2: =MIN(E2,I2)

I think that's it.

Best regards,
Trowa
oduana777 7Posts Thursday January 12, 2017Registration date March 23, 2017 Last seen - Feb 13, 2017 at 08:34 PM
Hi Trowa,
What you gave me worked great!

I have redesigned the formulas to remove negative integers but I have a few problems I don't know how to fix. I hope you don't mind helping me again!

Basically I mathematically manipulating two basic sets of data to compare them with each other after running them through some division and percentages.


After both sets are done I want to compare them to see what is the highest value between the two sets (MAX?)

SET A has values 85% of the time and SET B has values 55% of the time and 15% of the time both SETS A&B are blank cells with no data. How do I tell Excel to ignore the data set that has no data this time and just give me the value of the set that does have data?

Here is some messages I getting:


SET A is ((AF2/AE2)*100 THEN THAT IS DIVIDED BY AG2 NOW COLUMN X
SET B is ((AM2/AE2)*100 THEN THAT IS DIVIDED BY AN2 NOW COLUMN AJ

I NEED THE LARGEST NUMERICAL VALUE BETWEEN THE RESULTS THAT ARE COLUMNS X AND AJ WITHOUT EXCEL DOING A #DIV/0! BECAUSE OF THE BLANK CELLS IN EITHER COLUMNS AF OR AM.

I TRIED COLUMN AP AS =MAX(X2,AJ2) BUT THE ERROR MESSAGES FROM THE BLANKS IN AF OR AM STOP IT FROM FUNCTIONING.

THANKS SO MUCH!!!

OdUANA 777
Reply
Leave a comment
Helpful
+0
plus moins
Hi Oduana777,

You can combine the IF formula with the ISERROR formula to take care of the error message. For example =(IF(ISERROR(YOURFORMULA),YourFormulaWhenErrorOcurred,YourFormulaWhenItDoesn't)



But I am a bit confused by your example. You say that either AF or AM can be blank, but those values are the upper part of the division and thus won't create the error (0/1=0).

So I'm assuming that either AE, AG or AN can be blank.
Set A formula: =IF(AE2="","",(AF2/AE2)*100)
X formula: =IF(AG2="","",((AF2/AE2)*100)/AG2)

Or combine them in one formula:
=IF(OR(AE2="",AG2=""),"",((AF2/AE2)*100)/AG2)

Hopefully I understood you correctly and this helps you out.
Do let me know how this works out for you.

Best regards,
Trowa
oduana777 7Posts Thursday January 12, 2017Registration date March 23, 2017 Last seen - Feb 14, 2017 at 04:31 PM
Hi Trowa!
Sorry for the confusion.
Here goes on the individual columns.
AE always has a value that range from 4 to 75.
AF has a value 85% of the time. The other 15% is blank.
V = ((AF2/AE2)*100)
x = (V2/AG2)

When AF has no value (blank) V turns it into 0.00.
When AF has no value (blank) X turns into #DIV/0.
Both V and X are in the number category on Home page.
AG is blank 15% of the time.
This group is the SET A I was referring to.

SET B
AE always has a value from 4 to 75.
AI = ( (AM2/AE2)*100)
AJ = (AI2/AN2)
AN2 is blank 45% of the time.
AM2 is blank 45% of the time.

AP is the comparison between the result of X and AJ.
AJ= =max(X2,AJ2)
Right now AP only works when both sets AF & AG AND AM & AN have values and are NOT blank. When either set A or B is blank, the message is once again #DIV/0!

Thanks so much for your help! I am trying to analyze the relationships between them but cant start until I get all these crazy errors off the sheet!
Once it works I suspect a high AF and a low AG score should produce a definite correlation for success. The same should ring true for a high AM and a low AN.

AP is suppose to give me the highest of the two sets compared. Then I need to test these numbers to see if my theory is real.

Please give me the specific formula to remove these pesky zeros and #DIV/0.
Why column V and AI insist on turning blank cells into zeros I don't understand.

Your are my best (and only) Excel friend! Thanks so much for your time.

Oduana777
Reply
Leave a comment
Helpful
+0
plus moins
Hi Oduana777,

"Why column V and AI insist on turning blank cells into zeros I don't understand."

That's because formula wise, blanks are the same as 0's. Type an "=" in one cell, select an empty cell, hit enter and the result will be 0.

The following formula's will replace 0's and errors with blanks:
V2: =IF(AF2="","",(AF2/AE2)*100)
X2: =IF(ISERROR(V2/AG2),"",V2/AG2)
AI2: =IF(AM2="","",(AM2/AE2)*100)
AJ2: =IF(ISERROR(AI2/AN2),"",AI2/AN2)
AP2: =IF(MAX(X2,AJ2)=0,"",MAX(X2,AJ2))

Best regards,
Your Excel buddy ;)
oduana777 7Posts Thursday January 12, 2017Registration date March 23, 2017 Last seen - Feb 21, 2017 at 04:30 PM
Hi Trowa,

Once again your suggestions worked perfectly!!

If you are having fun with this I still have more to ask! If not tell me to stop
bothering you. You've already helped me more than anyone else!

The divisor in columns AG and AN are two large after number 1. They are making the final score far to low from reality.

I want to make a new column that looks at AG and AN which both have a range of values from 1 to 12.
I want 1 in both columns to stay 1 as a divisor. But raw data #2 is now 1.25 instead of 2 and raw data #3 is 1.5 instead of 3 and #4 is 1.75 instead of 4 and 5 is 2 and 6 is 2.25 and 7 is 2.5 all the way to 12 added .25 to each higher number. That way AG and AN are dividing V2 and AI 2 by smaller numbers that are equally separate by .25.

Basically, I want the new column to look at AG and AN and reassign their values per my numbers above. Also if the current scheme is not weighted properly I may have adjust the values 1.25, 1.5, 1.75 to other values later if this new scheme doesn't better reflect reality..

Thanks Again,

Oduana777
Reply
Leave a comment
Helpful
+0
plus moins
Hi Oduana777,

Well, I'm still having fun so here we go.

I think it's best to create a table to alter the values. You can place this table out of sight or on another sheet. But the idea is to put the values 1-12 in one column and 1-3.75 in the second column. Then use VLOOKUP to pull the data from that table.

So let's say sheet2 range A1:B12 is used for the table. Then the formula in the new column would look like:
=VLOOKUP(AG2,Sheet2!$A$1:$B$12,2)

This way you can easily alter the values in the second column of the table without having to change the formula's.

Best regards,
Trowa
oduana777 7Posts Thursday January 12, 2017Registration date March 23, 2017 Last seen - Mar 23, 2017 at 07:57 PM
Trowa,

You ARE the Excel master. The Array works!
Question: Can you make cells in any column turn a certain color if the values land within a certain range?
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!