### Array Formulas

Excel has a powerful feature called Array Formulas. Let’s see an example on how they can be used to improve our worksheets. We have the following data:

We have a list of articles, with it’s unit price and quantity that was sold. If we want to calculate the total sales value, we need to multiply each unit price for the quantity and then sum all of the values. We could add a column to the right of the Qtd column where we put a formula on cell E3 this formula =C3*D3 and then copy down until row 8. Then we need to sum all of the values on column E with a formula like =SUM(E3:E8) to get the sales total, right? Your sheet would look like this:

As you can see, we needed to make the calculation in two steps: first calculate the sales value for each article and then sum all of the sales values. What if we could do this with a single formula? Wouldn’t it be better? With array formulas this is possible. Let’s see how. Let’s get back to our original table, without the Sales Value column. We want to multiply the value of each row on column C by the value on that row on column D. If we put a formula like =SUM(C3:C8*D3*D8) on cell D10 and press Enter, you will get a #VALUE error:

Go back to the formula on cell D10 (press F2 to edit) and, instead of pressing Enter, press Ctrl+Shift+Enter at the same time. Your formula will look like this now:

Excel automatically adds the braces ({ }) to identify that this is an array formula. Don’t put the braces yourself because Excel will not interpret like an array formula this way! You always have to Ctrl+Shift+Enter each time you edit your array formula and Excel will add the braces for you. Now you just need to format the cell D10 value as currency.

Let’s see another example of array formulas use. Imagine that we wanted to see what a 5% increase on the quantity of articles sold would do for our total sales value. So wee need to multiply the Qtd column by 1.05 and then multiply that for the unit price value and sum all of the values for each article. Again, we have multiple operations and we want to make them on a single formula. We will use an array formula for that also, like this:

We start by multiplying each value on column D by the value on cell D11 (the 5% increase), then multiply that value for the value of column C and sum all of the results for each row.

As you can see by this two simple examples, Excel is able to handle multiple operations on a single formula. This will help you simplify your worksheets calculations, avoid using additional columns or rows to perform intermediate calculations, and improve your worksheets performance.

#### 20 comentários:

Anonymous said...

Outstanding! Thanks.

Unknown said...
Unknown said...
Unknown said...
Mamdouh said...
sikat said...

Use this article to increase your knowledge . cara menggugurkan kandungan

رواد الحرمين said...
Unknown said...

This blog is so nice to me. I will continue to come here again and again. Visit my link as well. Good luck
cara menggugurkan kandungan
cara menggugurkan kandungan

Unknown said...

https://buyusermedinafurniture.wordpress.com/2016/01/26/%D8%B4%D8%B1%D9%83%D8%A9-%D8%B4%D8%B1%D8%A7%D8%A1-%D8%A7%D9%84%D8%A7%D8%AB%D8%A7%D8%AB-%D8%A7%D9%84%D9%85%D8%B3%D8%AA%D8%B9%D9%85%D9%84-%D8%A8%D8%A7%D9%84%D9%85%D8%AF%D9%8A%D9%86%D8%A9-%D8%A7%D9%84/

https://buyusermedinafurniture.wordpress.com/2016/01/26/%D8%B4%D8%B1%D9%83%D8%A9-%D8%B4%D8%B1%D8%A7%D8%A1-%D8%A7%D9%84%D8%A7%D8%AB%D8%A7%D8%AB-%D8%A7%D9%84%D9%85%D8%B3%D8%AA%D8%B9%D9%85%D9%84-%D8%A8%D8%A7%D9%84%D9%85%D8%AF%D9%8A%D9%86%D8%A9-%D8%A7%D9%84/

عندك اثاث مستعمل تريد بيعة عندك اثاثك قديم وتريد تجديد اثاث البيت محتار وتبغى
شركة شراء اثاث مستعمل بالمدينه المنوره

شركة شراء اثاث مستعمل متخصصة فى شراء الاثاث المستعمل بالمدينة المنورة
كل ماعليك هو الاتصال بنا افضل شركة لشراء الاثاث المستعمل بالمدينة المنورة بافضل الاسعار اتصل بنا يصلك مندوبنا ليرى الاثاث ويقوم بخبرتة بتقييم الاثاث المستعمل
لديك ويعطى كل قطعة قيمتها ويعطيك افضل سعر بالسوق وبذلك تكون قد اتممت الصفقة باتصال واحد وتاتى العمال والسيارات لنقل الاثاث المستعمل لديك
واعطائك افضل الاسعار فى اثاثك المستخدم لماذ التقوى

شراء الاثاث المستعمل باالمدينة المنورة

لشراء الاثاث المستعمل بالمدينة المنورة افضل شركة شراء اثاث مستعمل بالمدينة المنورة لانها اولى الشركات العاملة فى
مجال شراء الاثاث المستعمل بالمدينة المنورة

https://elasmr16.wordpress.com/2017/11/02/%D8%B4%D8%B1%D8%A7%D8%A1-%D8%A7%EF%BB%BB%D8%AB%D8%A7%D8%AB-%D8%A7%D9%84%D9%85%D8%B3%D8%AA%D8%B9%D9%85%D9%84-%D8%A8%D8%A7%D9%84%D9%85%D8%AF%D9%8A%D9%86%D8%A9-%D8%A7%D9%84%D9%85%D9%86%D9%88%D8%B1%D8%A9

شراء الاثاث المستعمل باالمدينة المنورة

شراء الاثاث المستعمل بالمدينة المنورة

شراء الاثاث المستعمل بالمدينة المنورة

حراج المدينة المنورة

حراج المدينة المنورة

https://elasmr16.wordpress.com/2017/11/02/%D8%B4%D8%B1%D8%A7%D8%A1-%D8%A7%EF%BB%BB%D8%AB%D8%A7%D8%AB-%D8%A7%D9%84%D9%85%D8%B3%D8%AA%D8%B9%D9%85%D9%84-%D8%A8%D8%A7%D9%84%D9%85%D8%AF%D9%8A%D9%86%D8%A9-%D8%A7%D9%84%D9%85%D9%86%D9%88%D8%B1%D8%A9/

gunardi said...

Banyak sekali kelebihan tangki panel fiberglass, yang diantara lain adalah mudah dalam pemasangan tangki, tidak mudah terkena lumut dalam tangki panel, tidak cepat keruh dan berbau, serta tangki panel fiberglass ini ramah lingkungan
Tangki Fiberglass
Jual Septic Tank
Tangki Kimia
Jual Talang Fiber
Jual Rotameter
Tangki Panel
jual mesin ro
Jual Botol Plastik Agro

Jual Botol Plastik

Unknown said...

Thank you for a informative post.
clipping path
clipping path service
remove white background
car editing

Image background remover said...

Using the Images Background Remover service, you can get the best possible solutions. The photos will be livelier and you can use them anywhere. The importance of Background Remover Online is undeniable for every photo. Before we move to the benefits of having the service, it is better to know about the service.

Photo Retouch Services said...

Best Photo Retouch Services information is here for you go our site to read the ultimate guide to photo retouching service and image retouch

Clipping Path Service said...

Perfect Clipping path service means a lot to this professional business purpose. Do Visit to have more knowledge about photo clipping path.

Tangki Fiber said...
Jewelry Photo Retouching Services said...

Jewelry photo retouching services in place and you will be tiered to search them out. We have made it easy for you. Browse our site to take such a great opportunity right now. You will be amazed to know them!

Logo Desging said...

Logo is the the symbol or representation of an Organization or company that identify that organization. Logo designing is to create that symbol..Logo Desging Adobe illustrator or photoshop is a powerful tool for logo designing. A suitable logo represents the activities, motive, aim of that organization in a visual way simply.

al ghadeer said...
ahmed said...

De même, les punaises de lit pourraient faire de l'auto-stop après un film et rentrer chez vous contre leur hôte d'origine.
شركة مكافحة حشرات بالطائف
شركة مكافحة النمل الابيض بالطائف
شركة كشف تسربات المياه بالطائف

shaban osama said...

Cleanliness is one of the important things and attention must be paid to the constant cleaning of the place and care in order to have a house free from dust and dirt, as having a completely clean place is one of the things that every housewife seeks for and our company is used
شركة تنظيف بعنيزة

شركة تنظيف مجالس بعنيزة

شركة مكافحة حشرات بعنيزة

شركة نقل عفش بعنيزة

شركة كشف تسربات المياه بعنيزة