Trying to create a multi-column formula.
Solved/Closed
oduana777
Posts
7
Registration date
Thursday January 12, 2017
Status
Member
Last seen
March 23, 2017
-
Feb 4, 2017 at 06:11 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 28, 2017 at 11:07 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 28, 2017 at 11:07 AM
Related:
- Trying to create a multi-column formula.
- Create skype account with gmail - Guide
- Number to words in excel formula - Guide
- Create snapchat account - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Excel grade formula - Guide
4 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Feb 6, 2017 at 12:01 PM
Feb 6, 2017 at 12:01 PM
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
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Feb 14, 2017 at 11:40 AM
Feb 14, 2017 at 11:40 AM
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
Feb 14, 2017 at 04:31 PM
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
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
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Feb 16, 2017 at 11:32 AM
Feb 16, 2017 at 11:32 AM
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
Feb 21, 2017 at 04:30 PM
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Feb 23, 2017 at 11:14 AM
Feb 23, 2017 at 11:14 AM
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
oduana777
Posts
7
Registration date
Thursday January 12, 2017
Status
Member
Last seen
March 23, 2017
Mar 23, 2017 at 07:57 PM
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?
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?
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Updated on Mar 28, 2017 at 11:07 AM
Updated on Mar 28, 2017 at 11:07 AM
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
Feb 13, 2017 at 08:34 PM
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