Automatically order totals in columns and help with data units.

[ScampsDad]

Hello folks,

Like quite a few of the thread results in the forum I am trying to create a spreadsheet for a fishing clubs website. I have the basics all set up but would like to perform a couple of tweaks to finalize it.

I will attach the a screenshot and the Excel file to this post for info.

The two functions I'd like to add are as follows.....

1: Make the rows order automatically according to the total at the end of the row. In other words row with highest total to position one and so on.

2: The column figures calculate in decimal units and I would like to change these to work in pounds and ounces, whole numbers and 16ths where one pound equals 16 ounces.

I hope this makes sense but if not just holla!

Quick edit, the two columns titled Fish 1 and Fish 2 represent the two types of fish species to be included. Others may be added or taken away from time to time.


Many thanks, Chris

[Richard Buttrey]

What are the numbers. Ozs, lbs, Kg...?

Precisely how would you want to display the result. e.g.
Given 40.07 what result would you expect?

[ScampsDad]

Morning Richard and thanks for the reply.

The figures in the spreadsheet represent pounds and ounces. At the moment the sheet is adding them in decimal units. If you look at row three for example its making the sum 51.09+21.15=72.24. If it were in pounds and ounces (units of 16 ounces per pound) then that total should read 73.08 if that makes sense.

[AliGW]

There is probably an easier way, but try this in C11:

=SUM(INT(C2:C9))+INT((SUM(C2:C9)-SUM(INT(C2:C9)))/16*100)+(SUM(C2:C9)-SUM(INT(C2:C9))-INT((SUM(C2:C9)-SUM(INT(C2:C9)))/16*100)*16/100)

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[JohnTopley]

Try

in D2

=INT(C2)+(MOD(C2,1)+INT(MOD(C2,1)/0.16)*(0.84))

Copy down

Copy to E and H

NOTE: G2=SUM(C3,E3)

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[AliGW]

Thanks for the rep. Please mark the thread as solved as advised above.

[ScampsDad]

Hi again everyone

Thanks again for all of your help. At the risk of being a pain its almost done.

I have applied the formula that Ali suggested and modified it to work with columns E & G but the total by row in column G were still working in decimals. The total at the bottom of the column is fine. So I have applied the formula provided by Ali to the rows by adding
=SUM(INT(C2:C9))+INT((SUM(C2:C9)-SUM(INT(C2:C9)))/16*100)+(SUM(C2:C9)-SUM(INT(C2:C9))-INT((SUM(C2:C9)-SUM(INT(C2:C9)))/16*100)*16/100)but replacing (C2:C9) with (C2:F2) then the next row (C3:F3) and so on and this has reworked the row total perfectly. Secretly pleased I figured that one out!

The only thing left now as mentioned in the OP, I would like to apply a function to the spreadsheet to automatically order the input in order of total weight. Greatest being at the top and the lowest at the bottom.
Ive attached the file that I have modded so far.

Many thanks, Chris

PS: Ignore the file that is attached, I accidentally uploaded the original.

[Richard Buttrey]

Hi,

Personally I'd create a normalised 2 dimensional database. You'll then have the enormous power of the Pivot Table functionality to view your data in any way you want, and avoid any formulae or macros altogether.

Create a three column database with the following columns

Name
Fish Type
Weight.

The PT includes a calculated field to convert the totals to lb/oz

See attached

[Richard Buttrey]

..I should have added to try experimenting and dragging the Fish Type field to different places in the PT Field list
e.g.
1. Underneath the name in the row labels area of the PT field list to see fish types liste under each name
2. Above the name in the row labels area of the PT field list to see names listed under each fish type
3. To the column labels area to see fist types in separate column for each name

[JohnTopley]

Using Ali's formula the total of 536.01 is wrong: it should be 530.13 (as per Richard and my files).

[ScampsDad]

Apologies for the absence in responding but I have had a nasty case of man flu which any of my fellow males can confirm is almost life threatening!

Getting back on topic though and at the risk of putting the cat amongst the pigeons... I find Ali's formula works. Just by manually adding the ounces into a total in each column and dividing them by 16 I get a remainder in ounces which when added back to the pounds total matches with the results using Ali's formula.
I'm pretty sure this is correct and done it a few times on all 3 columns to make sure.

So if I was to carry on using Ali's formula is it then possible to make the rows auto arrange according to the totals in G2 to G9 with the highest total in G2 then going down in order of weight.

many thanks, Chris

**Original work sheet attached**

[AliGW]

Thanks, Chris - I thought I was going a bit mad when I saw that they boys' calculations didn't match mine.

Hopefully they can tweak my formula to work in the way you want - I am afraid I don't have much time today.

[AliGW]

Apologies - I have just accidentally deleted the last post to this thread. Sorry!

[JohnTopley]

Ali: don't worry: Your calculations are correct!


I can see where both Richard and I were wrong so apologies to you.

[AliGW]

So sorry, John.

[JohnTopley]

Like a dog with a bone ...!!!

slightly shorter


=SUM(INT(C2:C9))+INT(SUM(MOD(C2:C9,1)*100)/16)+MOD(SUM(MOD(C2:C9,1)*100),16)/100

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[AliGW]

That's shorter!

[ScampsDad]

Sorry folks, taking a step backwards here. The attached file is another spreadsheet Ive been working on. All it needs is for the columns to total in pounds and ounces.

I have tried using Ali's formula that she posted and have modified it for each column to suit.....

=SUM(INT(D3:D156))+INT((SUM(D3:D156)-SUM(INT(D3:D156)))/16*100)+(SUM(D3:D156)-SUM(INT(D3:D156))-INT((SUM(D3:D156)-SUM(INT(D3:D156)))/16*100)*16/100)

I thought I had this sussed but now I am getting errors every time I try to implement it.

All I am doing is selecting the column and placing the formula in the formula bar but alas, it doesn't work.

Any advice please?

Cheers, Chris

[JohnTopley]

Did you enter the formula with Ctrl+Shift+Enter?

Answer is 1509 lbs 3 ozs with Ali's formula and the one I posted in post #17

[ScampsDad]

Did you enter the formula with Ctrl+Shift+Enter?

A classic case of Ockam's Razor

Thanks John, that was exactly the problem

Chris

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.