# Dumb math question [calculating an index]

#### mv0087

##### New Member
How would I structure a formula to calculate an index based off a number that can be negative or positive?

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
For example, ...?

For example, ...?

So if target = 3% and actual is 3% the index is 100.

If target = -3.29% and actual is 2.05% the index is 162.

If target = -2.86% and actual is -.023% the index is 99.

Last edited:
Sorry that math is off...my brian is fried ATM.

Code:
``````      --A--- ---B--- ---C---
1   Target Actual   Index
2       3%      3%     100
3   -3.29%   2.05%   160.5
4   -2.86% -0.023% 12434.8``````

C2 and down is =100*ABS(A2/B2)

I guess that is what I am not following, how can a variance of 5.34 have a lower index than a variance of <3?

Pardon??

Resonding to: "I guess that is what I am not following, how can a variance of 5.34 have a lower index than a variance of <3?"

Mr. mv0087,

shg has already provided an excellent example of an index as well as the algorithm behind it.

Be careful when trying to compare one variance system over another when the targets are different. If all of the targets were the same then you could compare or if the underlying algorithm was based on the standard deviation then you could compare even if the targets were different.

Replies
9
Views
152
Replies
6
Views
206
Replies
1
Views
111
Replies
6
Views
78
Replies
1
Views
258

1,203,486
Messages
6,055,705
Members
444,809
Latest member
mwh85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back