Excel

Resolved · Urgent Priority · Version 2016

Mon 18 Dec 2017, 16:14 Delegate Vassilis said...

Vassilis has attended:
Excel Advanced course

Excel

I am trying to use an averageif formula. I need to average certain categories. I have a category e.g. sandwiches from column A-E and I have merged those cells, hence the values below are under sandwiches. When I insert the formula, it takes the value only from column A, instead of the range selected. This is because I wrote "sandwiches" on column A, but I have merged it cells until column E.
How do I fix this problem without unmerging the cells and writing in each column the word "sandwiches"?
Excel does not seem to detect that "sandwiches" is for columns A-E.

Mon 18 Dec 2017, 16:29 Trainer Jens said...

RE: Excel

Hi Vassilis,

Thank you for the forum question.

Merged cells ruin the worksheet. I hope that Microsoft removes this tool from Excel.

Click in the merged cell and unmerge the cell. Type "sandwiches" in the first cell you had merged before. Select all the cells you had merged on click on the down arrow in the bottom right corner in the alignment group on the Home tab. Select "Center Across Selection" under Horizontal Text Alignment. This will do the job without ruin the worksheet.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 18 Dec 2017, 16:51 Delegate Vassilis said...

RE: Excel

Hello Jens,

I did this but still it is not working. The thing is that if I select column B, it is like I haven't written the word "sandwiches".
So I would like excel to know that "sandwiches" is from column A-E without having to write it on every cell.

Mon 18 Dec 2017, 20:32 Edited on Mon 18 Dec 2017, 20:46 Trainer Jens said...

RE: Excel

Hi Vassilis,

Sorry I didn't understand you right. You cannot do what you want without typing "Sandwiches" in all the cells a to e.

But what about something like this:

=AVERAGE(IF($A$1="sandwiches",A2:A5,0))

Where A2:A5 is the range.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Hide separate columns in Excel 2010

If you want to hide columns not adjacent to each other for example, Columns A, C and E then:-

1) Click on the fist column to be hidden i.e. A

2) Press and hold down the CTRL key

3) While holding the CTRL key, left click on the rest of the columns you want to hide i.e. C and E

4) Right click and choose Hide

View all Excel hints and tips