Saturday, November 21, 2020

Running Total/ Cumulative Sum in Excel

 

Hey Guys! You should have noticed that I'm writing on MS Excel for last few days! To be honest I'm trying to learn some simple and useful tricks of Excel. And I'm also trying to share my knowledge with you. In this post I've come with such a little trick which maybe very useful if you face such situation. Let's begin . . . 

 

Running Total/ Cumulative Sum

It means the summation of numbers in a list and it will be updated each time when a number is added or changes. Look at the image below - 

 


Look at the image. There are two columns with numbers. The first one is simply the number list. But the 2nd one is the summation of the list. Each time a new number is added to the first column the second one shows the summation.


How to do this? 

It's not a magic. Very simple sum formula is used to perform this task. But there is a little trick. You just need to lock the first cell and then every time it will update the running total list. 


Here is my list. Simply type some numbers in your Excel. In my list there are 10 numbers ranging from F7 to F16. And I've used column G for running total calculation. In G7 I've used this formula - 

 =SUM($F$7:F7)

Or this will work too  =SUM(F$7:F7)

Why $ sign? Yes that's the magic. In Excel $ sign is used to lock a cell. In G7 this formula will add F7 with F7 itself. But in G8 this formula will add F7 with F8. In G9 this formula will add F7, F8 and F9. 

Know why? Cause you've locked first F7 cell by putting dollar sign. But the second F7 is not fixed. When you drag the formula the first F7 cell will remain in its position and the second F7 cell will be converted to F8, F9, F10 . . . .


How do you put $ sign

You can lock the cell manually by simply typing dollar sign. But there is a comfortable keyboard shortcut you can use. This is F4. Put your cursor in the formula. Place it before the cell number which you wanna lock. Now press F4 button to automatically put the dollar sign. Do this like - 

=SUM(IF7:F7)

Look at the red sign in the above formula. Suppose it's your cursor. Take your cursor at this position and press F4. And it will lock your first F7 cell like below - 


=SUM($F$7:F7)

If you type manually then you don't need to put $ before F. You can just put $ before 7. This will do the same task. 


Project File (Running Total/ Cumulative Sum

 

You can also download the project file from the above link. 


Hopefully this will help you in your work. Feel free to contact us if you face any problem regarding this tutorial. And obviously thanks for visiting Marks PC Solution . . .


Friday, November 06, 2020

Joining Texts from Multiple Cells in Excel

Combining Texts in MS Excel

 

Programs like MS Excel is full of functions. There are many useful functions that we don't use or we don't know about. One of them is CONCATENATE. It means joining or linking. With this function you can join texts from multiple cells. Let's see how . . .


Suppose you're working in an Excel Worksheet where you need to combine texts from different cells. I'm gonna show you an example. Let's consider the image below - 

 

CONCATENATE Functions in Excel

What do you see? I've imputed First Name and Last Name. In Full Name column I've used the formula i.e. CONCATENATE. And in the last column I've added Mr. with the full name. 

 

So how do you use this formula? Here it is - 

=CONCATENATE(A2," ",B2)

Observe it carefully. You can add more than 02 cells with this functions. Here I've just used 02 cells. You may think what is this " " for? You may notice that there is a space between the two i.e. " & ". 

If you don't use " " then your full name will be like AbdurRahim. But two words should be separate. And for this weed need to put space. But in computer you should know space is a kind of character. That's why we need to put space between this quotation " " mark.  


Now you have the full name in column C. If you wanna put Mr. before the full name then you can do it this way - 

=CONCATENATE("Mr. ",C2)

Since we have the full name in Column C we can simply put "Mr. ",C2 within the bracket. You should notice that there is a space after Mr. so that we get the name as Mr. Abdur Rahim. 

 

There is a problem! 

You should have noticed that we've used two different columns for joining the names with Mr. But you can also do this in a single column. If you use the formula as below then you can do this task at once - 

=CONCATENATE("Mr. ",A2," ",B2) 

If you put the formula like this then you don't need the first two formula. Cause this one will join your names with Mr. without needing any additional column like the 2nd formula. 

 

Combining Texts in MS Excel using CONCATENATE function

 

That's the screenshot of my work. And I've also uploaded the soft copy in Google Drive. You can download it from the link below - 

 

Project File (CONCATENATE)

 

If you face any problem or notice anything wrong in this post don't be late to inform us through comment box. 

 

Stay well and stay with Marks PC Solution for cool tips, tricks and tutorials . . .


Saturday, October 31, 2020

Facebook থেকে ভিডিও ডাউনলোড করার সহজ উপায়

 
আপনারা অনেকেই হয়তো বিষয়টি জেনে থাকবেন। কিন্তু যারা এখনো জানেন না তাদের জন্য আজকের এই লেখা। হ্যা। আজকে আপনারা দেখবেন কিভাবে সহজেই ফেসবুক থেকে আপনি আপনার পছন্দের কোন ভিডিও ডাউনলোড করে নিতে পারেন। 
 
সত্যি বলতে কি তেমন কোন বিশেষ জ্ঞান আপনার দরকার নেই এটা করার জন্য। Google Play তে অসংখ্য Apps রয়েছে যারা মাধ্যমে আপনি Facebook থেকে ভিডিও নামাতে পারবেন। আপনি চাইলে আপনার পছন্দ মত যে কোন App বেছে নিতে পারেন। তবে আমার কাছে FastVid অ্যাপটি ভাল লেগেছে। আর সেজন্যেই আপনাদের সাথে শেয়ার করা . . .

তো চলুন কথা না বাড়িয়ে কাজে নেমে পড়ি। প্রথমেই Google Play তে গিয়ে FastVid লিখে সার্চ করুন। তাহলেই আপনার সামনে সেটা চলে আসবে। অথবা সরাসরি নিচের ছবিটিতে ক্লিক করুন। 

FastVid App Icon

এরপর এটা ডাউনলোড করে নিন। নিজে থেকেই ইন্সটল হয়ে যাবার কথা। এবারে App টি চালু করুন। নিচের মত একটা স্ক্রীন আসবে। 

FastVid App Screen


প্রথম অপশনটিতে আপনাকে ফেসবুকে সাইন ইন করতে বলবে। সেখানে আপনি আপনার IDPassword দিয়ে সাইন ইন করবেন। তারপর আপনি সেখান থেকে নিজের মন ভিডিও নামিয়ে নিতে পারবেন। কিন্তু আপনি যদি নিরাপত্তার স্বার্থে অ্যাপটিতে নিজের ফেসবুক দিয়ে সাইন ইন করতে না চান সেক্ষেত্রে আপনাকে পরের অপশনটিতে যেতে হবে। 

ফেসবুক একাউন্ট দিয়ে সাইন ইন না করতে চাইলে আপনি URL অপশনটিতে চলে যাবেন। সেখানে গেলে এই পেজটি আসবে - 
 

FastVid Facebook URL Downlaoder


লক্ষ্য করে দেখুন এখানে একটি বক্স আছে। যেটাতে আপনি ভিডিও লিংক কপি করে বসাতে পারবেন। এক্ষেত্রে আপনাকে প্রথমেই ফেসবুক থেকে আপনার পছন্দের ভিডিও লিংকটি কপি করে আনতে হবে। এরপর এখানে পেস্ট করে দিবেন। ডাউনলোড বাটন প্রেস করার পর এই স্ক্রীন টি আসবে - 

FastVid Download Options


এখানে তিনটি অপশন দেখা যাচ্ছে। আপনি যদি High Quality Video নামাতে চান তাহলে HD অপশনটি চেপে দিন। আর যদি মোটামুটি মানের হলেই কাজ চলে তাহলে SD বেছে নিন। এতে করে আপনার Mobile Data সাশ্রয় হবে। আর চাইলে ভিডিও টি ডাউনলোড না করে শুধু দেখতেও পারবেন। সেজন্য আছে Watch অপশন। যদিও এটার দরকার নেই। কারণ দেখতে হলে আপনি ফেসবুকেই দেখতে পারেন। এখানে আসার দরকার নেই। 

আজ এই পর্যন্তই। আশা করি অ্যাপটি আপনাদের ভাল লাগবে। অথবা আপনাদের কারো যদি ভাল কোন App জানা থাকে তাহলে অবশ্যই শেয়ার করতে পারেন কমেন্ট বক্সে। 

Marks PC Solution এর সাথে থাকার জন্য ধন্যবাদ।


 

Saturday, October 24, 2020

MS Excel Functinos (LEFT, MID, RIGHT, LEN, MID+LEN)

 

Left, Mid, Right, Len Functions

Hello guys! In this post I'm gonna write about some basic functions of Excel i.e. left, right, mid, len & mid+len. Normally they look really simple. But they can be of great use if you know how to combine them with other functions . . . 

 

In this tutorial I'm not gonna discuss about complex use of these functions. I will simply show you the basic use of them. Hopefully next time you will see the advanced use of these functions. 

 

Excel sheet with left, mid, right, len functions

Let's start with the image above. There are mainly 04 formulas i.e. Left, Mid, Right & Len. And the next 02 are combinations of Mid and Len functions. 

I've tried to explain every formula in this image. I guess nothing is ambiguous with these 04 functions. 

You might be confused with the combinations. In the 1st combination the 02 functions are simply merged. 

But in the 2nd combination you should notice that len function is not used. Instead we've used the len column i.e. G2. That is the difference. 1st combination requires to merge len function with mid. It doesn't require any other column. But 2nd combination is using a different len column instead of merging len function with the mid. 


Well I'm not interested to discuss any longer. I've uploaded the project file. You can download it from here - 

 

Project File (LEFT, MID, RIGHT, LEN)

 

You should practice by yourself and I hope you will get this easily. If you face any problem then comment below. Thanks for staying with Marks PC Solution . . .

 

 

Rounding Numbers in MS Excel

 

Rouding Numbers in Excel

 

If you work with fraction numbers in Excel then you might be familiar with this problem. While summing up fraction figures in Excel the result may vary from manual calculation. This happens because Excel counts every digits after the decimal point even if they don't appear on the screen! 

 

To fix this problem you can round the fraction numbers as per your requirements. Today I will talk about some simple ways to round the numbers. Hope this will solve your problem and save time as well . . .

 

Rounding the Numbers

First you should know that Excel has 03 formulas to round the numbers - 

  • Round
  • Rounddown
  • Roundup

Depending on your necessity you can choose any of them. Let's begin . . .

 

Rouding Numbers Formula in Excel

Now I'm begging your attention to the above image. I've tried to show all the formulas in a single worksheet. 

Look at the original numbers and their summation. And then look at the values of rounded numbers and their summations. There are some differences. Now it's your duty to decide which type of rounding you will follow. 

For accurate calculation I will suggest you to use normal rounding formula. But depending on your work, you can also choose between roundup and rounddown.

 

For your convenience I've also uploaded the project file in my Google Drive. You can download it from here - 

Project File (Rounding Numbers)

 

You should remember that this is just a basic tip for rounding numbers. There are many more advanced tasks to do in Excel. 

For example if you wanna round up before the decimal point then you can use a negative sign i.e. =Round(A2,-1) or =Round(A2,-2). But in that case your value should be 03 digits or more. 

 

Hopefully I will talk more about rounding next time. Thanks for staying with Marks PC Solution . . .