Excel question...

[reds4ever]

Can someone give me the way to obtain the following result.

If I put (say) 200 in A1, putting either 100 or 300 in B1 results in the the same value being put in C1, a score to refect how close the two numbers are (A1 and B1).

Putting 20 in A1 and 10 or 30 in B1 should result in the same score in C1 as the example above, so it needs to be a percentage.

All results in C1 should be positive.

Cheers

[VetLegion]

C1=(A1/B1)*100

[reds4ever]

Originally posted by VetLegion
C1=(A1/B1)*100

When A1=200 and B1=300 then C1 =66.666

When A1=200 and B1=100 then C1 = 200

They need to give the same value in C1.

[VetLegion]

You wrote the number of the beast

THIS THREAD IS DOOMED

[VetLegion]

Frankly, I probably didn't understand what you need. Perhaps someone else can help.

[reds4ever]

LOL!!

Can I have an answer before the Christians lock it?

[VetLegion]

Explain what you need more precisely.

[Whaleboy]

Are you looking for some kind of difference function, that returns the difference between two numbers, or are you looking for an average.. i.e. Average of 100 and 300 = 200, average of 150 and 250 = 200 etc.

A difference function would calculate 200 and 100 respectively.

[Zopperoni]

I don't get it. What's the relationship between A, B, and C?

[VetLegion]

Ah, I think I know. You need the difference, but you need the absolute value

In that case, try C1=abs(A1-B1)

[Petek]

How about

C1 = ABS(A1 - B1)/ABS(A1)

?

If you want cell C1 to display a percentage (instead of a number), right-click on C1, select Format Cells, then select Percentage and set how many decimal places to display.

[Provost Harrison]

That's what I thought he meant, but he worded it in such an awful way it took me a while to figure it out

[reds4ever]

Originally posted by VetLegion
Ah, I think I know. You need the difference, but you need the absolute value

In that case, try C1=abs(A1-B1)

Cheers.

Now I need it give the result as a percentage of A1.

Its just a simple 'closest guess' scoring sheet for a league I'm organizing.

EDIT Cheers Petek!!

[Saras]

What, no hlookup pissing matches?

[reds4ever]

This still ain't working....how do I get the answer in C1 as a percentage of A1, not an absolute percentage.