Solved

# table data based on adjoining calculation

Posted on 2014-09-26
83 Views
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
0
Question by:Pedrov664
• 6
• 4

LVL 29

Expert Comment

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

Author Comment

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

LVL 29

Expert Comment

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

LVL 29

Accepted Solution

gowflow earned 500 total points
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

Author Comment

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

0

Author Comment

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

LVL 29

Assisted Solution

gowflow earned 500 total points
ok here is the solution when I remove the columns and recreate them.
gowflowEE-gamestats-2014-V01.xlsm
0

LVL 29

Expert Comment

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

LVL 29

Expert Comment

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

Author Comment

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

## Featured Post

### Suggested Solutions

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.