table data based on adjoining calculation

Hi everyone,

The attached file in sheet named 'CurrentGame2' I would like to update the "Better ERA" section using data from the sheet to the left of it.

For instance, V6:W6 should contain data from the team that has an ERA of -1 or greater comparing data in cells E8 and M8 (i.e. the first two teams). The cells have been manually filled in to show the desired results.
The formula should be able to be copied down.

Please note that when the numbers in E8 and M8 do not differ by one or greater then it is left blank. (i.e. V8:W11)
EE-gamestats-2014.xlsm
Pedrov664Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Could you explain again the logic for filling the Table "Better ERA" as nothing is filled in the file you attached. Please specify what columns need to be filled and how exactly the data is compiled as you point to row 6 then comparing it with row 8 !! (Why 8 and not 6 ?)
gowflow
0
Pedrov664Author Commented:
Gowflow,

Attached is a file that contains data in V:W. Those are the columns and rows that I would like to extract data to from B:Q in the 'currentgame2' sheet.

The reason row 8 is used is because it contains the most recent stats (i.e. last 5) Row 6 contains last 10 stats.

Hope this helps,
Pedro
EE-gamestats-2014.xlsm
0
gowflowCommented:
Sorry its chineese to me with all do respect to chineese I do not know this language.

If you want my help you need to explain to me how you get these figures as not clear to me at all. You explain how you go about so I can get you the formula.

I think you made a mistake and the first line
you have put
MIL      8-32-1.78

where I think you meant
MIL      8.32-1.78

meaning negative 8.32 and 1.78 positive.
What I don't get is how you get these figures I guessed that
8.32- = 3.44 - 11.76
but fail to understand the 1.78 I see it is the difference on row 7
so what is the relation with all this ???

Furthermore I try to put formula in Col V W but I cant it puts the formula and not the results !!! what did you do although I looked at options and it says in advanced to display results and not formula.

gowflow
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

gowflowCommented:
Well here is the formulas:

I added them in the attached file at the bottom of the table in the green section as I could not get the formula to display results in col VW like advised before.

The formula for the team is
=IF(INDIRECT("E" & 4*ROW(E5)-15)-INDIRECT("M" &4*ROW(M5)-15)<0,INDIRECT("B" & 4*ROW(B5)-15),INDIRECT("J" & 4*ROW(J5)-15))

The formula for the results is
=ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15)-INDIRECT("M" &4*ROW(M5)-15),2))&" - "&ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15+2)-INDIRECT("M" &4*ROW(M5)-15+2),2))

When you fix the issue of getting the results in col V and W then you take these 2 formulas that you copy from here and paste into cell V6 and W6
and from there you drag them down.

You will notice that I removed the blank row that you had between the 2 tables and the new header as it was mixing the row count.

Also notice that you have a value in M19 and M20 that shows Div0 I guess you should know why this is happening and should correct this and the formula will give the result automatically.

Same you have from row 53 downward errors in formulas I guess your missing results here that when you update will also give you the correct data in the formula.

I hope this is what you need else let me know
gowflow
EE-gamestats-2014.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pedrov664Author Commented:
Goflow,

This is an interesting problem since I have not seen this problem before where a formula does not work in adjacent table.

Thanks for your help. I have not been able to see how to solve that problem. For that reason I have posted for someone to help here.

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28529937.html

I will wait to see what others have to say about this issue.
0
Pedrov664Author Commented:
BTW,

 M19 and M20 that shows Div0 is because the pitcher does not have enought stats. That is normal since excel is trying to divide by 0 and lets me know it cannot do that.

and row 53 downward errors in formulas are also expected since formulas are looking for results and cannot find. When results are there these problems are corrected and results are shown.

The above problems are expected and is normal considering the formulas used in the table.
0
gowflowCommented:
ok here is the solution when I remove the columns and recreate them.
gowflowEE-gamestats-2014-V01.xlsm
0
gowflowCommented:
Glad we got it to work at the end.

BTW, my assumption is that you have so many formulas in this wb and some of the get REF error some of them also hv errors for missing data and sum hv different type of errors ....

Maybe at some point in time when creating these formulas don't really know if you did all this or got help form EE experts ... at some point in time someone must hv turn the option to show formula and not results to troubleshoot something ... and somewhere the cell were changed into forting TEXT as this is what I found in these columns so all these combinations with an Unexpected Excel 2010 that have some very weird behavior got locked somewhere in its memory that these columns we need to display formula and not results even though we are changing the entire Sheet behavior by toggling this option on and off it did not affect so the only possible solution at the end was to completely delete the columns removing all references if any in Excel backdoor memory !!!

This is my assumption but not sure either
Regards
gowflow
0
gowflowCommented:
Also as a side note I strongly suggest you to get assistance in cleaning these errors as the worksheet becomes more readable and less confusing.

If you need help with this let me know I will be glad to assist. For sure you will need to post something new not to over thread this question.
gowflow
0
Pedrov664Author Commented:
Goflow,

I did not correct these errors in the sheet I posted here because it was not relevant to the post. But I see it may cause confusion in solving other formula errors.

BTW, I have posted a corrected copy of the file with another question here, if you would like to look at it.

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28530722.html

Best Regards,
Pedro
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.