Related:

- Trying to create a multi-column formula.
- Excel If Then, create a new column ✓ - Forum - Excel
- How to create a count down formula ✓ - Forum - Office Software
- Excel create unique id column ✓ - Forum - Excel
- Excel formula to compare two columns and return a value ✓ - Forum - Excel
- Excel – Formula to calculate between two columns - How-To - Excel

TrowaD

- Posts
- 2673
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 26, 2020

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

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

TrowaD

- Posts
- 2673
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 26, 2020

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

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

- Posts
- 7
- Registration date
- Thursday January 12, 2017
- Status
- Member
- Last seen
- March 23, 2017

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

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

TrowaD

- Posts
- 2673
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 26, 2020

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

"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

- Posts
- 7
- Registration date
- Thursday January 12, 2017
- Status
- Member
- Last seen
- March 23, 2017

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

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

- Posts
- 2673
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 26, 2020

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

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

- Posts
- 2673
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 26, 2020

Thanks Oduana,

Your question isn't very clear to me.

Do you mean that when data is entered in column A, you want column B to change color for its respective row?

If so, then use conditional format (found under the Start ribbon) and choose to use a formula.

Formula: =$A1<>""

Apply to: =$B:$B

Best regards,

Trowa

Your question isn't very clear to me.

Do you mean that when data is entered in column A, you want column B to change color for its respective row?

If so, then use conditional format (found under the Start ribbon) and choose to use a formula.

Formula: =$A1<>""

Apply to: =$B:$B

Best regards,

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