*The tutorial provides a list of Excel basic formulas and functions with examples and links to related in-depth tutorials.*

Being primarily designed as a spreadsheet program, Microsoft Excel is extremely powerful and versatile when it comes to calculating numbers or solving math and engineering problems. It enables you to total or average a column of numbers in the blink of an eye. Apart from that, you can compute a compound interest and weighted average, get the optimal budget for your advertising campaign, minimize the shipment costs or make the optimal work schedule for your employees. All this is done by entering formulas in cells.

This tutorial aims to teach you the essentials of Excel functions and show how to use basic formulas in Excel.

Before providing the basic Excel formulas list, let's define the key terms just to make sure we are on the same page. So, what do we call an Excel formula and Excel function?

**Formula**is an expression that calculates values in a cell or in a range of cells.For example,

`=A2+A2+A3+A4`

is a formula that adds up the values in cells A2 through A4.**Function**is a predefined formula already available in Excel. Functions perform specific calculations in a particular order based on the specified values, called arguments, or parameters.

For example, instead of specifying each value to be summed like in the above formula, you can use the SUM function to add up a range of cells: `=SUM(A2:A4)`

You can find all available Excel functions in the **Function Library** on the *Formulas* tab:

There exist 400+ functions in Excel, and the number is growing by version to version. Of course, it's next to impossible to memorize all of them, and you actually don't need to. The Function Wizard will help you find the function best suited for a particular task, while the **Excel Formula Intellisense** will prompt the function's syntax and arguments as soon as you type the function's name preceded by an equal sign in a cell:

Clicking the function's name will turn it into a blue hyperlink, which will open the Help topic for that function.

What follows below is a list of 10 simple yet really helpful functions that are a necessary skill for everyone who wishes to turn from an Excel novice to an Excel professional.

The first Excel function you should be familiar with is the one that performs the basic arithmetic operation of addition:

SUM(*number1*, [number2], …)

In the syntax of all Excel functions, an argument enclosed in [square brackets] is optional, other arguments are required. Meaning, your Sum formula should include at least 1 number, reference to a cell or a range of cells. For example:

`=SUM(B2:B6)`

- adds up values in cells B2 through B6.

`=SUM(B2, B6)`

- adds up values in cells B2 and B6.

If necessary, you can perform other calculations within a single formula, for example, add up values in cells B2 through B6, and then divide the sum by 5:

`=SUM(B2:B6)/5`

To sum with conditions, use the SUMIF function: in the 1st argument, you enter the range of cells to be tested against the criteria (A2:A6), in the 2nd argument - the criteria itself (D2), and in the last argument - the cells to sum (B2:B6):

`=SUMIF(A2:A6, D2, B2:B6)`

In your Excel worksheets, the formulas may look something similar to this:

The Excel AVERAGE function does exactly what its name suggests, i.e. finds an average, or arithmetic mean, of numbers. Its syntax is similar to SUM's:

AVERAGE(number1, [number2], …)

Having a closer look at the formula from the previous section (`=SUM(B2:B6)/5`

), what does it actually do? Sums values in cells B2 through B6, and then divides the result by 5. And what do you call adding up a group of numbers and then dividing the sum by the count of those numbers? Yep, an average!

The Excel AVERAGE function performs these calculations behind the scenes. So, instead of dividing sum by count, you can simply put this formula in a cell:

`=AVERAGE(B2:B6)`

To average cells based on condition, use the following AVERAGEIF formula, where A2:A6 is the criteria range, D3 is he criteria, and B2:B6 are the cells to average:

`=AVERAGEIF(A2:A6, D3, B2:B6)`

The MAX and MIN formulas in Excel get the largest and smallest value in a set of numbers, respectively. For our sample data set, the formulas will be as simple as:

`=MAX(B2:B6)`

`=MIN(B2:B6)`

If you are curious to know how many cells in a given range contain **numeric values** (numbers or dates), don't waste your time counting them by hand. The Excel COUNT function will bring you the count in a heartbeat:

COUNT(value1, [value2], …)

While the COUNT function deals only with those cells that contain numbers, the COUNTA function counts all cells that **are not blank**, whether they contain numbers, dates, times, text, logical values of TRUE and FALSE, errors or empty text strings (""):

COUNTA (value1, [value2], …)

For example, to find out how many cells in column B contain numbers, use this formula:

`=COUNT(B:B)`

To count all non-empty cells in column B, go with this one:

`=COUNTA(B:B)`

In both formulas, you use the so-called "whole column reference" (B:B) that refers to all the cells within column B.

The following screenshot shows the difference: while COUNT processes only numbers, COUNTA outputs the total number of non-blank cells in column B, including the the text value in the column header.

Judging by the number of IF-related comments on our blog, it's the most popular function in Excel. In simple terms, you use an IF formula to ask Excel to test a certain condition and return one value or perform one calculation if the condition is met, and another value or calculation if the condition is not met:

IF(logical_test, [value_if_true], [value_if_false])

For example, the following IF statement checks if the order is completed (i.e. there is a value in column C) or not. To test if a cell is not blank, you use the "not equal to" operator (<>) in combination with an empty string (""). As the result, if cell C2 is not empty, the formula returns "Yes", otherwise "No":

`=IF(C2<>"", "Yes", "No")`

If your obviously correct Excel formulas return just a bunch of errors, one of the first things to check is extra spaces in the referenced cells (You may be surprised to know how many leading, trailing and in-between spaces lurk unnoticed in your sheets just until something goes wrong!).

There are several ways to remove unwanted spaces in Excel, with the TRIM function being the easiest one:

TRIM(text)

For example, to trim extra spaces in column A, enter the following formula in cell A1, and then copy it down the column:

`=TRIM(A1)`

It will eliminate all extra spaces in cells but a single space character between words:

Whenever you want to know the number of characters in a certain cell, LEN is the function to use:

LEN(text)

Wish to find out how many characters are in cell A2? Just type the below formula into another cell:

`=LEN(A2)`

Please keep in mind that the Excel LEN function counts absolutely all characters **including spaces**:

Want to get the total count of characters in a range or cells or count only specific characters? Please check out the following resources.

These are the two most popular logical functions to check multiple criteria. The difference is how they do this:

- AND returns TRUE if
**all conditions**are met, FALSE otherwise. - OR returns TRUE if
**any**condition is met, FALSE otherwise.

While rarely used on their own, these functions come in very handy as part of bigger formulas.

For example, to check the test results in columns B and C and return "Pass" if both are greater than 60, "Fail" otherwise, use the following IF formula with an embedded AND statement:

`=IF(AND(B2>60, B2>60), "Pass", "Fail")`

If it's sufficient to have just one test score greater than 60 (either test 1 or test 2), embed the OR statement:

`=IF(OR(B2>60, B2>60), "Pass", "Fail")`

In case you want to take values from two or more cells and combine them into one cell, use the concatenate operator (&) or the CONCATENATE function:

CONCATENATE(text1, [text2], …)

For example, to combine the values from cells A2 and B2, just enter the following formula in a different cell:

`=CONCATENATE(A2, B2)`

To separate the combined values with a space, type the space character (" ") in the arguments list:

`=CONCATENATE(A2, " ", B2)`

To see the current date and time whenever you open your worksheet without having to manually update it on a daily basis, use either:

`=TODAY()`

to insert the today's date in a cell.

`=NOW()`

to insert the current date and time in a cell.

The beauty of these functions is that they don't require any arguments at all, you type the formulas exactly as written above.

Now that you are familiar with the basic Excel formulas, these tips will give you some guidance on how to use them most effectively and avoid common formula errors.

Once you have typed a formula into a cell, there is no need to re-type it over and over again. Simply copy the formula to adjacent cells by dragging the **fill handle **(a small square at the lower right-hand corner of the cell). To copy the formula to the whole column, position the mouse pointer to the fill handle and double-click the plus sign.

For the detailed step-by-step instructions, please see How to copy formulas in Excel.

When you remove a formula by pressing the Delete key, a calculated value is also deleted. However, you can delete only the formula and keep the resulting value in the cell. Here's how:

- Select all cells with your formulas.
- Press Ctrl + C to copy the selected cells.
- Right-click the selection, and then click
*Paste Values*>*Values*to paste the calculated values back to the selected cells. Or, press the Paste Special shortcut: Shift+F10 and then V.

For the detailed steps with screenshots, please see How to replace formulas with their values in Excel.

Any text included in your Excel formulas should be enclosed in "quotation marks". However, you should never do that to numbers, unless you want Excel to treat them as text values.

For example, to check the value in cell B2 and return 1 for "Passed", 0 otherwise, you put the following formula, say, in C2:

`=IF(B2="pass", 1, 0)`

Copy the formula down to other cells and you will have a column of 1's and 0's that can be calculated without a hitch.

Now, see what happens if you double quote the numbers:

`=IF(B2="pass", "1", "0")`

At first sight, the output is normal - the same column of 1's and 0's. Upon a closer look, however, you will notice that the resulting values are left-aligned in cells by default, meaning those are numeric strings, not numbers! If later on someone will try to calculate those 1's and 0's, they might end up pulling their hair out trying to figure out why a 100% correct Sum or Count formula returns nothing but zero.

Please remember this simple rule: numbers supplied to your Excel formulas should be entered without any formatting like decimal separator or dollar sign. In North America and some other countries, comma is the default argument separator, and the dollar sign ($) is used to make absolute cell references. Using those characters in numbers may just drive your Excel crazy :) So, instead of typing $2,000, simply type 2000, and then format the output value to your liking by setting up a custom Excel number format.

When crating a complex Excel formula with one or more nested functions, you will have to use more than one set of parentheses to define the order of calculations. In such formulas, be sure to pair the parentheses properly so that there is a closing parenthesis for every opening parenthesis. To make the job easier for you, Excel shades parenthesis pairs in different colors when you enter or edit a formula.

If all of a sudden your Excel formulas have stopped recalculating automatically, most likely the *Calculation Options* somehow switched to *Manual*. To fix this, go to the *Formulas* tab > *Calculation* group, click the *Calculation Options* button, and select **Automatic**.

If this does not help, check out these troubleshooting steps: Excel formulas not working: fixes & solutions.

This is how you make and manage basic formulas in Excel. I how you will find this information helpful. Anyway, I thank you for reading and hope to see you on our blog next week.

## 327 Responses to "Basic Excel formulas & functions with examples"

I want to know if I type in the Cell/B2(Jan-18) and now i want to type the Cell/B2 in the Cell/D32 what the formulas or solution if any...

Virender:

Enter =B2 in cell D32.

Ok that is for one cell And if I need the same value to be added in Cell/ D33, D44, D55 and so on.

Dear Sir/ Mam,

--

I like information, you providing,, Further request you to send me detail formulas specially for the condition "IF".I think it will help in future.

--

Kedar

Hi Kedar,

Please check out the following tutorials:

Using IF function in Excel

Excel IF with multiple AND/OR conditions

Excel nested IF statement

can you help me in above formula i am applying this formula but didnot show good results

Good morning. Is there a way to add letters into a cell after a simple sum calculation? ie I sum a list of hectare totals and now I want to add "ha" after. Thanks

=SUM(L8:L161)

Hi Tom,

You can use the CONCATENATE function or concatenate operator (&) like this:

=SUM(L8:L161)&"ha"

If you want a space between the number and text, use this formula:

=SUM(L8:L161)&" ha"

Please note, concatenation turns the output into a text string, and you won't be able to use the result in further calculations.

how to display sheet 2 value in sheet 1 by formula

=SUMIF(DF!B:B,SFD!C5,DF!F:F)

=SUMIF(range,criteria,sum_range)

Thanks, very useful piece of information

thanx for this information

Hi Svetlana,

I am trying to figure out how to get excel to recognize if a cell populates or not.

I want cell G39 to read cell G40. G40 has a formula that sometimes populates from a data worksheet. When it populates I want G39 to display cell G3. If it doesn't I want it to stay blank.

What I have tried so far and both have not worked:

=IF(NOT(ISBLANK(G40)),G3,"") This doesn't work because it reads the formula so the cell isn't really blank.

=IF(G40"",G3,"") This seems to have the same problem.

Not Sure what else to try...

Typo in the second formula which should read:

=IF(G40"",G3,"")

Still doesn't work

Your board isn't posting the greater than and less then signs that would come after the G40 and before the "" in the 2nd formula. That formula still doesn't work with those included.

Hi Jared,

Sorry for messing around with your formula, it's because of a silly bug in our blog engine that we are unable to fix.

=IF(G40<>"", G3, "") seems to work fine for me except when G40 returns an error. That is:

if G40 has a value, G39 displays G3.

if G40 is an empty string, G39 is blank (empty string).

if G40 has an error, G39 displays an error.

What is wrong about this behavior? Please clarify.

Hi,

How to use pipe formula for series of invoice NOs.

D0123

D0124

D0125

D0126

D0127

Hi Svetlana,

The (G40"",G3,"") would work if I didn't have a formula in G40. I think the formula that's puling data from a different tab was being recognized.

I was able to find a work around. Instead of using cell G40 I went to the Tab I was getting the information and went to the cell that G40's formula was populating from. This is what I came up with that worked:

=IF(Data!B34"",G3,"")

Thanks,

Jared

FANTASTICS

hi, may I know that if a column have 2 words, but I want to separate it to 2 columns, what is the fomula?

E.g:

in column:

123456 abc

then I want to separate 123456 and abc to 2 columns.

Thanks for the help in advance.

Al:

If you don't have too many of these things to separate, then just use the Text-to-Columns tool.

Under Data select the Text-to-Columns and with your data use the Delimited option and select the space checkbox as the delimiter. That will separate the data into two columns.

Otherwise where the data is in A1 you might want to use RIGHT(A1,3) to get the abc characters into a cell.

There are several techniques to split text strings. AbleBits has a couple of good articles that explain some of these techniques.

easy to understand this function. lot of thanks

5290 x 69 + 10%

Laxmikant:

=(5290*69)+((5290*69)*0.01)

Good five

please send me simple formula for excel in my mail id .

thanks

RAMNIVAS VERMA

9315509039

V=0.30/3[A1+A2]ROOT[A1XA2]

Thanks Very Useful

Dear Sir

These Formulas are really helpfull .Please give detail info of If Formula.

Hi Yogesh,

Please check out these tutorials:

IF function in Excel with formula examples

Excel IF statement with multiple AND/OR conditions

Can you use formulas to calculate hours worked? I do lot of timesheet work and be good to know. For example 7.50am start to 6.20pm finish ?

Thanks

Hi Loubie,

Please check out the following tutorials:

How to calculate time in Excel

How to add & subtract time to show over 24 hours, 60 minutes, 60 seconds

If 1kg of tomato costs 200 rupees, what will be the cost of 700 grams

how to calculate in excel what formula use for this problem

MD:

I think what you're after is:

(200*.7)/1000

1 kg tamato =200

1kg=1000gm

1 gm= .2 (200/1000)=.2

so,

cost of 700gm

=700gm*.2rs=140

sir i convert to numeric to text formulla,

means 549556 i change auto five lac fourty nine thousand five hundred fifty six only

Help me understand the formulas of MS-Excel

I have a spreadsheet with imported values from a bank statement. It’s saved as a spreadsheet not csv data. The first column is the date, the second is the transaction the third is the amount etc till the sixth which is my own description that I added. Is there a way to pick the whole line out and place it in another row with all the same data? Example, the last column could be house, apartment, nm house, condo or trailer park.

Sub Button1_Click()

Dim Str As String

StrFolder = "C:\Users\heywh\Videos\Assorrted Credits\"

ActiveWorkbook.FollowHyperlink Address:=StrFolder, NewWindow:=True

End Sub

How would i use an If Statement in the above Macro

I want to show a msgbox so that if the c:\folder above nothing is selected with in the sub folder to this msgbox "No Month Was Selected" show in a MsgBox and if a Month is selected it goes right to the month selected.

Thank You For your Time.

plz gv mi da formula of RANKing

Hi Ronald,

Please check out this tutorial:

Excel RANK functions with formula examples

Nice sir ji

i need one formula if we entering the employer code that page have to show name and his detail's

Dear Svetlana,

i have gone through yours tutorial it is amazing thanking you too make our life easy. i have one more request from you, i do asset validation checking all the asset is correctly captured such that they can be processed in software to make life easy to our organization.

Please suggest me number of formulas and Technic i can used to do my validation more accurate.

currently i am using vlookup, pivot table, mid, clean, trim, iferrror, and many more.

I want know how hloockup work in excel sheet and what is the use of this formulas .

Hi Neil,

Please check out this tutorial:

How to use HLOOKUP in Excel

SIR WITH EXAMPLES SHOW IN EXCEL HOW TO CALCULATE COMPOUND INTEREST.

FOR EXAMPLE PRINCIPAL AMOUNT RS.10,00,000/-

DELAY DAYS 200 FOR WHICH INTEREST 15% PER ANNUM

COMPOUNDED QUARTERLY

INTEREST RATE 15% PER ANNUM COMPOUNDED QUARTERLY

SIR KINDLY DO THE ABOVE CALCULATION IN EXCEL AND GUIDE ME

Hi Latha,

You can find the detailed guidance on calculating compound interest in this article:

How to calculate compound interest in Excel

How can some one understand all this?

Hii

Excel sheet formula

Please information

Thanks , so helpful.

Hi,

I have filled the colour to the one of the cell in excel.

if I doubleclick the coloured cell. it should show the value which I assighed it.

my question: there is any formula to add value, example the value should be in background. by doubleclick the colourd cell, it shows the value.

thank you in advance

GOOD

Hello,

Can you please send me a link for a tutorial on creating a sales lead template with if and then being a 2 week window for contacting those leads?

How you use percentage formula in Excel.

Hi Dinesh,

You can find the detailed guidance here:

How to calculate percentage in Excel

I like information, you providing,, Further request you to send me detail formulas specially for the condition "IF".I think it will help in future.

Hi!

We have a lot of tutorials about different aspects of the IF function. Here's the entry point:

https://www.ablebits.com/office-addins-blog/tag/excel-if-statement/

I have a problem in my excel coding

(this coding

Function Rup(amt As Variant) As Variant

Dim FIGURE As Variant

Dim LENFIG As Integer

Dim i As Integer

Dim WORDs(19) As String

Dim tens(9) As String

WORDs(1) = "ONE"

WORDs(2) = "TWO"

WORDs(3) = "TRE"

WORDs(4) = "FUR"

WORDs(5) = "FIV"

WORDs(6) = "SIX"

WORDs(7) = "SVN"

WORDs(8) = "EIT"

WORDs(9) = "NIN"

WORDs(0) = "ZER"

WORDs(11) = "Eleven"

WORDs(12) = "Twelve"

WORDs(13) = "Thirteen"

WORDs(14) = "Fourteen"

WORDs(15) = "Fifteen"

WORDs(16) = "Sixteen"

WORDs(17) = "Seventeen"

WORDs(18) = "Eighteen"

WORDs(19) = "Nineteen"

tens(2) = "Twenty"

tens(3) = "Thirty"

tens(4) = "Fourty"

tens(5) = "Fifty"

tens(6) = "Sixty"

tens(7) = "Seventy"

tens(8) = "Eighty"

tens(9) = "Ninety"

FIGURE = amt

FIGURE = Format(FIGURE, "FIXED")

FIGLEN = Len(FIGURE)

If FIGLEN < 12 Then

FIGURE = Space(12 - FIGLEN) & FIGURE

End If

For i = 1 To 3

If Val(Left(FIGURE, 2)) 0 Then

Rup = Rup & WORDs(Val(Left(FIGURE, 2)))

ElseIf Val(Left(FIGURE, 2)) > 19 Then

Rup = Rup & tens(Val(Left(FIGURE, 1)))

Rup = Rup & WORDs(Val(Right(Left(FIGURE, 2), 1)))

End If

If i = 1 And Val(Left(FIGURE, 2)) > 0 Then

Rup = Rup & " Crore "

ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then

Rup = Rup & " Lakh "

ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then

Rup = Rup & " Thousand "

End If

FIGURE = Mid(FIGURE, 3)

Next i

If Val(Left(FIGURE, 1)) > 0 Then

Rup = Rup & WORDs(Val(Left(FIGURE, 1))) + " Hundred "

End If

FIGURE = Mid(FIGURE, 2)

If Val(Left(FIGURE, 2)) 0 Then

Rup = Rup & WORDs(Val(Left(FIGURE, 2)))

ElseIf Val(Left(FIGURE, 2)) > 19 Then

Rup = Rup & tens(Val(Left(FIGURE, 1)))

Rup = Rup & WORDs(Val(Right(Left(FIGURE, 2), 1)))

End If

FIGURE = Mid(FIGURE, 4)

If Val(FIGURE) > 0 Then

Rup = Rup & " Paise "

If Val(Left(FIGURE, 2)) 0 Then

Rup = Rup & WORDs(Val(Left(FIGURE, 2)))

ElseIf Val(Left(FIGURE, 2)) > 19 Then

Rup = Rup & tens(Val(Left(FIGURE, 1)))

Rup = Rup & WORDs(Val(Right(Left(FIGURE, 2), 1)))

End If

End If

FIGURE = amt

FIGURE = Format(FIGURE, "FIXED")

End Function

in this coding i want 0 gentrate as ZER its not coming its coming like 0 as 0 how to i get it

Display the highest and lowest marks in each test also give one appropriate leading

Hi, I'm tring to find a formela to divide the input value to the next cells as 100s.

example: if A2= 200, then B2=100, C2=100, D2=0 and E2=0

and if A2 changed to 300 then B2=100, C2=100, D2=100 and E2=0.

Best regard.

Update to clarify my request:

The "300" in the input cell could be increased manually to any number 500, 1200 ..... or 5000

A1 = "input cell", the next 12 cells B1 to M1: each cell should be filled with a 100 as long A1 increased by 100

if A1= 1043; B1=C1=D1=E1=F1=G1=H1=I1=J1=K1=100; but L1=M1=0 & N1=43

N1= A1-(sum(B1:M1)) if A1 >1200 or N1= 0 if A1 <= 1200

hi

could you please have you tel me where i get more information about condition formatting

Good

I have in a cell a range date for example

1-12 - 1-19 and I would like to change to a Jan-12 - Jan 19

What is the formula for it?

Thanks

Hello, Olga,

Thank you for your interesting question.

Please try the following formula:

=CONCATENATE(INDEX({"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"}, MID(A1, 1, FIND("-", A1, 1)-1)), MID(RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1), 1, FIND(" - ", RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1))+2),INDEX({"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},LEFT(MID(RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1),FIND(" - ", RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1))+3, 10),FIND("-",MID(RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1),FIND(" - ", RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1))+3,10))-1)),MID(MID(RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1),FIND(" - ", RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1))+3, 10),FIND("-", MID(RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1),FIND(" - ", RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1))+3, 10)), 10))

Please note that this is an array formula. You should enter this formula into a cell in any column and hit Ctrl + Shift + Enter to complete it. Copy the formula down along the column if necessary by selecting the cell where you've entered the formula and drag the fill handle (a small square at the lower right-hand corner of the selected cell) down.

Hope it will help you.

Good Afternoon,

Please show me the formula I need to use to add columns B11 thru H11 in addition to adding 10% all in one cell.

Thank you,

Noreen

Hello, Noreen,

If we understand your task correctly, please use the following formula to solve it:

=SUM(B11:H11)+SUM(B11:H11) *10%

Hope this is what you need.

Hi,

I have made up a spreadsheet with costings in column D (example). Each row in column D is of different value, how can I copy or create a formula that will multiply my markup of 38% (1.38) and then divide by currency (13). So for example =D12 is 12.75*1.38/13. Problem is that if I copy the formula from D12 all through to D23 it changes the value in each row to the value in D12 (12.75)

Hope this is making sense (oh my)

thank you so much

i have a small clarification regarding formula creation we creating some excel supplier wise part wise we want to count each supplier wise part wise

supplier:

1.america

2.africa

america Part details:

1.aa

2.bb

africa Part details:

1.cc

2.dd

Any formula for calculate of

If I have 80boxes, 1carton = 30boxes

I need answer is 2cartoon 20boxes

Can help me set the formula, thanks.

Sir / Mam

I want to write 06 in formula bar but it always show 6. Please any solution give me Sir.

Thanks

Hi Arif,

The easiest way is to type type '06 in a cell. To learn other ways, please check out How to add leading zeros in Excel

write like this '06

Sir I want to know the how to convert the no(56) into the letter(Fifty Six) in excel with using the formula of excel or using function of excel.

hello mam i have no knoweldge of excel.so you can tell , how do you use ecxel in the computer.

How am I use countifs in any data.

Please man /sir replayed me how can i use countifs cintex in any data.

Hi Sharda,

You can find the detailed explanation with formula examples in this tutorial:

How to use Excel COUNTIFS and COUNTIF with multiple criteria

Please tell me how to copy a cell value into another cell but if the original cell is a (-) figure it should now become (+) and vice versa

Hi Emma,

Use this Formula,

=If(F12>0,(-1)*F12,(-1)*F12)

where, F12 is the cell, which you want to copy.

Dear Sir/ Ma"am,

Will you please tell me, how to use macros?

Thanks in Advance

I am pretty new to Excel. I cant get this formula right. Please help.

In cell N25 from Excel Worksheet 2 below, write a function to sum the Account amounts in cells M8 to N22 and round the results to the 2nd digit. Write the function such that it can be copied or dragged down from cell N25 to cells N26 through N33. *

How to shipment# 1000199990030 formula function? It's short #19999 column down countn

I need help about how to compile a home household budget

Hi,

Please give me a solution in a excel formulas. 5026*50%=2513 but I want to show the amount with round 2500. like 6055*50%=3027.5 but it'll be 3000 and total amount will be 2500+3000=5500.

Thanks

pls send some useful formula for excel.

your work is so helpful to the world

Hi, anybody can help me

Iam customizing inventory file consisting table like this;

example:

Item SI# DR# QTY RUNNING TOTAL

if I input under SI# any text or number, the input qty will be added under running total;

then, if input under DR#, any text or number the input qty will be deducted under running total,

Please show me how to do the formulas.

Thanking you in advance.

PLEASE TELL ME FORMULAS OF ATTENDENCE-SHEET

Send me average formula in full form

Hello Sandip,

You can find full details about the Excel AVERAGE function in this tutorial:

How to calculate average in Excel

Dear Support, please can you assist with formula in excel to increment data in cell as follows: Cell A1 > 0 - 7 , Cell A2 > 8 - 15 , A3 > 16 - 23 etc...

i.e each time value to increase by 8.

Thank you for you valuable support and advise.

Regards.

I need day Calculation as like

Join Date- 01.01.2018

last date 03.06.2019

= 00 month 00 days

Hi I have a client with two spreadsheets. One has an inventory on it so the first column is the stock number for a certain product and then there is a quantity column further along.

The other tab is a Purchases column and we are wanting to be able to add a recurring stock number or new stock number and the quantity of product for that item just received. I am wanting to link the information from the Purchases tab to the inventory so that it will update the quantity list in the inventory stock list automatically.

Is there a formula for doing this?

Thank you in advance

Alina

Type in =vlookup.type the cell containing common column, press ,

Then drag all the data from the other sheet press , again. Then press the column position to which you want the data from. press , and type zero. Close parenthesis and but enter

I'm very thankful for those basic formula that i have seen and learned in this site, it such a big help in my day to day work and great thing that can develop my learning in excel. thankyou.

Hi,

I am trying to use the IF to creat a formula but the system is limiting to 9 Nine arguments. I would wish to create 17 arguments. Please assist.

Thank you

Formulas are very useful. I am using it from a long time. Thanks Ablebits.

70/28 and 30/12 how to Sum this two numbers

These are very useful

These are really very useful

How can we make Total of Particular Columns one colored cell (ie 'C' column's only red colored cell's value have to make total?

Good day

If I have a list price and a nett, how do I work out the discount?

Thank you

Simple question...What is the formula I should use if I want a column to list consecutive dates, such as 2001, 2002, 2003...2035, without having to type each year in a cell manually? It is similar to the @sum formula.

Hi Brittany,

You can use the Excel AutoFill feature for this.

Please gimme advice. I need one formula ex.cell a1 value -3 multiple b1 or cell a1 value 3 multiple c1

Suggest me a formula for this

if Cell A1 has value (27.5*6.25/2*2) i put formula in cell B1 =27.5*6.25/2*2 it shows value 171.88 but when i change value in cell a1 it should change in the value in cell B1

I must say that I am reading thru all comments, etc and I must say I am very impressed.

For me what I am working on is simple; 1 sheet has all my menu items with the before and after taxes, mark up 30%, 32%, menu price with g.s.t. and price without g.s.t.

2nd sheet has stock with price/numbers i.e. Carrots: 10#; 8.99$, 1 # cost x, 1 oz costs X

3rd sheet has all and I mean all recipe.

Simply put, I would like to enter i.e. cost of 1 # of carrots (from sheet 2 to sheet 3) into my recipe on sheet 2. Then the end cost (bottom line) import it into Sheet 1 under say Soup du jour. Just that simple.

I'll get there soon. p.s. really appreciate all those feedback's on Excell

Please give me advice how I can lock cell protect from VBA code.

>6000 DA is 15%

4000 to 6000 DA is 12%

<4000 DA is 10%

suggest me the formula

carry a total from one worksheet to another in the same workbook? Need formula, please

I want to know how to copy "now" formula for a whole row or column. By which i can get time exect to that related cell.

Tables!$D$14:$E$49, this is the formula that is seen on a cell of excel, can you explain to me how to track that table

how to convert date from number to dot format in excel

for uploading in SAP format

20161130 how to convert date from number to dot format in excel

i am tired this formula =date(left(A..4),mid(A..5,2),right(A..2)),I GOT 30.11.2016.But this format not supported SAP

Please Advice me, how to add personal details together (ex: name, designation office name, mob no, address )i have these each seperate columns, but in the final columns i want all together, what is the formula for these? i hope you understand what i'm asked here.

Hi, Im hoping someone may be able to help me with this.

Currently I have these columns:

C D E

2 Started Ended Days

3 08/07/2016 15/07/2016 7 (=DAYS(D3,C3)

4 08/07/2016 17 (=TODAY()-C4)

I have a formula in another square to count the number of completed investigations that were completed in less or equal to 28 days - =COUNTIFS(E3:E4,"<=28")

However, I only want the formula to count those investigations that have been completed, so essentially only where the cells in column D is not blank, it has a date. This will then allow me to show current investigations and time taken to date, plus a count of completed investigations based on meeting KPI's.

Any help you can provide would be appreciated. Thank you in advance.

how can i create A New Sheet In a single excel file

Hi

I want to write 265324 Lac as 2.65 Lac in open office.

Pl. help with the formula.

Thanks

How to change from S12223 to S122-23?

i wanted to ask whats is the logic of *1 or +1 in below mentioned formula for example =DATE(2019,MID(B2775,4,2)*1,LEFT(B2775,2)*1)

How to salary in excel

Same example while using shows error.

formula used is =(AVERAGEIF(A2:A8,E2,B2:B8)) but error shows as #DIV/0!

Same example while using shows error.

formula used is =(AVERAGEIF(A2:A8,E2,B2:B8)) but error shows as #DIV/0!

i want to calculate expenses, if container is of 20ft then the total amount should be different, and if container is of 40ft then also the amount should come different. can anyone tell which formula to use as it should be in one cell only.

I want to Calculate, A Value of 2323.56 it should covert to nearest ten value @ 2330 can anyone tell which formula to use.

Ms excel word sheet pdf