6 Replies Latest reply on Dec 19, 2014 7:29 AM by Matthew Peterson

Excel-like equation

Good morning,

I can't seem to get a field to calculate like I would in Excel.

I want the first cell to simply be equal the column to its left and each cell below to equal one cell divided by the sum of that cell and another cell.

For example, G is the column I'm trying to calculate and the following are the numbers in G that I WANT but I'm not getting:

A          B          C          D          E          F          G
0%       145       436       145       436       75%      75%

1%       130       178       275       614       58%      69%

2%       57         67        332        681       54%      67%

.          .            .           .            .           .            .

.          .            .           .            .           .            .

.          .            .           .            .           .            .

100%  648       457       3732       2471      41%      40%

So, I want G at 0% to be equal to F at 0%.  After that, I want G in any row to be equal to E#/sum(D#:E#) but when I use any equation I can think of like the following I'm either getting an error or I'm getting the exact same numbers I have in the F-column:

if attr([A]) = 0 then [F]

elseif attr([A]) > 0 then (E/(E+D)

end

Thanks!

Matt

• 1. Re: Excel-like equation

Matthew:

Can you share some data, so someone can help you out. You need to do table calculations for that and also how the data is laid out.

..kk

• 2. Re: Excel-like equation

Hi KK.  I hope this helps.

A:

IF round([random1],2) < [random1]

THEN round([random1],2) + .01

ELSEIF round([random1],2) >= [random1] then round([random1],2) end

B:

if[random2]>=0 then 1 else 0 end

C:

if[random2]<0 then 1 else 0 end

D:

if[random2]>=0 then 1 else 0 end

E:

if[random2]<0 then 1 else 0 end

F:

sum([C])/sum(([C]+[B]))

G:

if attr([A]) < .005 then [F]

elseif attr([A]) >=.005 then (([E])/([E]+[D]))

end

This is where D and E are re-calculated under G to add sum(D) and sum(E), respectively.

Also, D and E are the same calculations as B and C, except they are cumulative totals.

Thanks again

• 3. Re: Excel-like equation

Matthew:

What are the Random1 and Random2 values?

..kk

• 4. Re: Excel-like equation

These are Metrics.  For example, if I was doing football statistics, these would be QB rating and Yards per play.

• 5. Re: Excel-like equation

if you have that data, can you provide so we can help you out than creating the sample data.

..kk

• 6. Re: Excel-like equation

Good morning.  I finally found the solution.  It was simply a click of the drop-down under the measure value, compute table (down).  This changed the results from what I already had to the aggregate results.

Thanks!