MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Please Assist - Simple math operation, I think!


Posted by Steve Kalynuik on August 18, 2000 2:47 PM

My daughter is looking forward to getting a game for the Nintendo 64 called Ogre Battle 64 and it has starting questions, using a mathimatical equation to produce a starting group of characters.

So far I have been able to in excel, ask the 6 questions, and from the answers produce 4 totals to those questions, so in G13 is the total for Fighters, H13 is the totals for Magic Users, I13 is the total for Specialist, and J13 is the total for Tamers.

What I would like help on is, with the totals from G13, H13, I13, and J13 I have to identify the highest of the 4, that will represent group #1. Then from that highest number subtract 32, now the remaining highest is Group #2, then from that set of totals 32 is subtracted from the highest and group #3, and once more 32 is subtracted to produce group #4.

eg. From questions answered the following results:
Fighter =36
Magic-User =76
Specialist =36
Tamer =56
From above 76 being the highest, Group #1 is Magic-User, subtract 32 from the highest and get:
Fighter =36 =36
Magic-User =76-32=44
Specialist =36 =36
Tamer =56 = 56
From above 44 being the highest, Group #2 is Tamer, subtract 32 from the highest and get:
Fighter =36 =36
Magic-User =44 =44
Specialist =36 =36
Tamer =56-32 =24
From above 44 being the highest, Group #3 is Magic-User, subtract 32 from the highest and get:
Fighter =36 =36
Magic-User =44-32=12
Specialist =36 =36
Tamer =24 =24
From above 44 being the highest, Group #4 is Fighter, there is a tie of 36 but order dictates preference, Fighter being first.

I am using Excel 97, Windows 98.

This may be alot to ask but please point me in the right direction.


Posted by Ivan Moala on August 20, 0100 12:25 AM


Hi Steve
As no one has answered yet and it's for your daughter, try the following;

In G14 =IF(G13=MAX(G13:J13),G13-32,G13)
In H14 =IF(H13=MAX(G13:J13),H13-32,H13)
In I14 =IF(I13=MAX(G13:J13),I13-32,I13)
In J14 =IF(J13=MAX(G13:J13),J13-32,J13)

Then copy these formulas down as far as you need
NOTE: 1) doesn't takeinto account duplicate no's
BUT you can adj manually for this.
2) This is just one way to do it to get
you started.


HTH

Ivan