• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

New Formula with a differen logic ??

akinkaraman

Member
In my sheet 2nd row calculates the total of the weights of Upper Deck's pallets (Blue section) due to their Zones and 3rd row calculates the total of the weights of Lower Deck's pallets (Orange section) due to their Zones.

What I need is a formula which gets the total weigts of the zones. I wrote manually the cells names with Sum formula but all pallet weights have their zone names at 2 line belows of their weights and can any formula calculate it without writing all palets's cell's names?
 

Attachments

  • sheet.xlsx
    48.2 KB · Views: 16
Hi ,

I am sure others will respond with answers ; I will only comment.

The required formula is either cumbersome to enter , as you have found out , or it will be complex and time-consuming to develop.

The reason for this is that the worksheet has been designed either to look good on screen or formatted to be printed out.

This is not the way worksheets should be developed ; there should be a clear demarcation between input data entry areas , data processing areas , and output display areas.

Once you have the data in a simple format , all the formulae / calculations become easy. Once you have the output , you can have it displayed in any format that you want , using worksheet cells , merged or otherwise , textboxes , picture objects ,...

Here , the problem is because of merged cells. If you can unmerge the cells where the formulae need to be entered , it will become somewhat easier.

Narayan
 
Thank you for your response Narayan. This is a sheet for one type of aircraft and when I want to make a sheet for a bigger aircraft to find the correct zone's weights rhen writing them into the formula would be difficult. I thought to write the which zones they belong to under the weights and find out maybe a way to solve by a formula for my next aircraft sheet.

The cells are merged because they show the sizes of the palet types so unfortunately they should be as merged.
 
Hi ,

I understand the reason for the merged cells ; what I am trying to impress upon you is that if you have a separate area , either in the same worksheet , or in a different worksheet , where you do all the calculations , then you can insert simpler formulae in the merged cells ; for example , suppose you could have a simple formula for adding the pallet weights , then in the merged cell P2 , instead of the formula :

=SUM(P9+P15+P21+P27+P33)

you could have a formula such as Sheet2!P2.

The cell P2 in Sheet2 need not be merged , since Sheet2 is going to be used only for calculations , and the formulae can easily be copied across and down.

Narayan
 
I see. Okay if I make a second page without merged cells like in my new sheet, what should I write as a formula for Row 2 and Row 3 (for Upper Deck and Lower Deck) ?
 

Attachments

  • sheet.xlsx
    51.7 KB · Views: 6
Merged cells and their inherent problems have been pointed out before to akinkaraman but he prefers the look over the dangers they bring, although I did point out he could get the same look with centre selection, with no dangers.
 
Hello Dear bobhc. I don't use merged files in my other sheets but this sheet refers the palet sizes. As I told you before I have to use merged files in this sheet because of there are different palet types. So I can understand where the palet size starts and where it ends.

I also learnt to show the view like merged but without merging from you however it is difficult to use/write it in iPad when I use it at work (aircraft).

As Narayan says I am trying to make another page to calculate and send to my page which has merged cells now.

I don't know what I should do more. I am just a learner now. I am checking your formulas and trying to understand the logic and converting them to other needs day by day.

You guys are awesome and helping me to learn more. My thanks are not enough to you all..
 
I think I found a way to do without unmerge.

For P2
Code:
=SUMIF($P$12:$CO$12,P5,$P$9:$CO$9)+SUMIF($DE$12:$EV$12,P5,$DE$9:$EV$9)+SUMIF($P$18:$DT$18,P5,$P$15:$DT$15)+SUMIF($P$24:$DT$24,P5,$P$21:$DT$21)+SUMIF($P$30:$EQ$30,P5,$P$27:$EQ$27)+SUMIF($P$36:$CN$36,P5,$P$33:$CN$33)+SUMIF($CP$36:$ER$36,P5,$CP$33:$ER$33)

For P3
Code:
=SUMIF($T$44:$BC$44,P5,$T$41:$BC$41)+SUMIF($T$50:$BC$50,P5,$T$47:$BC$47)+SUMIF($T$56:$BC$56,P5,$T$53:$BC$53)+SUMIF($CH$44:$DE$44,P5,$CH$41:$DE$41)+SUMIF($CH$50:$DE$50,P5,$CH$47:$DE$47)+SUMIF($CH$56:$DE$56,P5,$CH$53:$DE$53)+SUMIF($DH$44:$EE$44,P5,$DH$41:$EE$41)

drag the formulas to other cells.
 

Attachments

  • sheet.xlsx
    49 KB · Views: 5
Last edited:
Back
Top