# Excel-Formulas: Ask Your Excel Questions To Experts

No one wants to get stuck while working on spreadsheets. A simple problem can take you hours finding the right solution which leads to even more frustration. For quick answers and reference on Excel how-tos, check out Excel-Formulas, a web service that connects boggled Excel users with experts to get quick, confidential and comprehensive help.

To start using Excel-Formulas, just type in your Excel question on the homepage. Your question could be as short or as detailed as possible. Then choose an expert type that you feel will best answer your question. The fields include Accounting, Human Resource, Inventory, Business, and Loans.

You can also select a General Expert if you don’t know who should handle your query – then enter your e-mail to submit the question. Response time depends on the volume of questions at a given time.

Excel-Formulas is a free service for small questions (if you don’t mind waiting), and you are only asked to pay for the complicated ones if you are satisfied with their answer. You can pay up to $50 if you need serious help. All your questions are archived in your account – and if you really need a quick answer, you can also ask within Excel using the Excel Formulas Expert Add-in.

This website is a great solution for those who can’t finish their work because of simple Excel problems they can’t solve. Instead of wasting time looking for answers online, a quick chat with real Excel experts will save you a lot of time – definitely a worthy service for people who deal with spreadsheets at work.

Features

- Provide quick answers to any Excel question.
- Choose your expert to best answer your question.
- Look back at all your questions in the account dashboard.
- Free for simple questions.
- Pay only when you are satisfied with their answer.
- Plugin allows you to ask question within Excel.

Check out Excel-Formulas @ www.excel-formulas.com

Code. Date. Status

El01. 12_10_20. Pass

El00. 7_10-20. Fail

El01. 13_10_20. Fail

How can we find out the status of El01 on 13_10_20?

Pls help. Excel 2010. In worksheet b i have different names and each have speciffic number, for ex, 1 flowers, 2 food, 3 nature, 4 animals etc. And i need to transfer or copy these names into correct cells in worksheet A. In work sheet A i have 1 row with numbers but they are not in order and same number are repeated many times, and another row is empty. So here in this empty row i need data from sheet 2, exact names for exact numbers. They are over 1000 so just copying doesnt work.

Hi, in sheet 1 I have data need to transpose and paste it in sheet2. Example : in A columns has in sheet 1

A B

1 a

1 b

1 j

2d

2 l

2 t

I need output in sheet2 like below.

A B

1 2

a d

b l

J t

Please guide me how to do it using VBA or any shortcut method for six thousand s rows data

Hi,

Please help me to solve the below problem

=COUNTIFS('Weld Map'!$O$6:$O$7105,"="&F2,'Weld Map'!$V$6:$V$7105,"=Rej*",'Weld Map'!$AL$6:$AL$7105,"=REJ*")

I need to count the text "REJ" from columns "V" and "AL" under the criteria of a period between F1 and F2

Hello, can you help me about this formula?

=IF(RIGHT(A1,2)<50,A1-RIGHT(A1,2),CEILLING(A1,100)

A1=1347

I have a tracker on Excel

I am trying to make it so I can calculate some cells based on another cells value, but only if those cells have numbers in them.

So

I want to format Cells A3:C3 to calculate if A3:C3 have numbers in them.

If they do then the colors will be based on Cells D3.

Example

C3=3 (or any number)... then excel colors C3 based on D3 value...(If D3=10-20 then C3 will be yellow, if D3=21-30 then C3 will be Green, if D3=0-9 then C3 will be red)... but if the C3 is blank or has a letter in it then C3 is gray

I have a sheet of hundreds of employees. In each row of the employee we have dates where at 6 months and 12 months there is a change. What formula do I use to highlight the WHOLE row when a date is 3 days away? I tried conditional formatting but only got as far as jus highlighting the one cell with the date. I would like the whole row to be highlighted. I do open this sheet everyday. Please provide possible formulas. The columns with the dates are columns P and T. What formula can I use to have the whole row highlighted when the time comes?

I have an excel spreadsheet with around 300,000 clients, each client are on separate rows, and each client has up to 15 phone nubmers; however many of these phone numbers are duplicated for the same client on the same row.

I would like to know how to remove only the duplicate phone number on each row and leaving the other duplicate phone number in the row?

Hello, can you help me determine the formula to show the following in cell BT16:

if L16-D16=0 show zero, if L16-D16=>0, show the result of L16-D16

I am looking for away to to set up a spreadsheet to track invoices and due dates. Then when they go past due (30 days) accrue 1.5% interest every month from that point on. Any ideas

A B C D

1 Sales Code Product Name

2 001 001 Product 1

3 002 002 Product 2

4 003 003 Product 3

5 002 004 Product 4

6 002 005 Product 5

My question is:

How can I get the product name(Data in D column) in B2 if A2 matches the data from the column C?

Hi.. sir

I need some solution for (Sumif) we can calculate using this function with text like stating number but if i need the calculation where starting names are different please see below

Item name Cost Price Selling Price Billing Price

L/M iphone 6s 2200 2250 2220

L/M htc 820 1500 1600 1650

Samsung Ace 1000 1100 1090

Samsung Tab 800 870 860

From this sheet first i want to take report which name star from L/M and other report would be letter starts without L/M.. how to make this please assist me.

Respected Sir,

i am facing problem to pick the right function for calculating the loan. my detail is as under.

A1 is principal amount

B1 is interest /mark up

C1 the amount paid by client

D1 the total unpaid interest

now i want a formula that if C1 is more then D1 them access amount should be minus from A1 if the C1 is less then D1 the C1-D1 and A1 remain same.

in actual loan paid less then original monthly markup but the remaining markup will not be add in principal amount because it is a bulk loan.

BANK DATE VOUCH NO CODE AMT

PNB 01/06/2016 RC-1 PL501 27000

PNB 01/06/2016 RC-2 PL193 20000

BOI 02/06/2016 RC-1 PL194 10000

AXIS 01/06/2016 RC-3 PL124 55200

CAN 03/06/2016 RC-1 PL001 48000

HDFc 03/06/2016 RC-2 PL005 10000

AXIS 01/06/2016 RC-4 PL025 44650

AXIS 01/06/2016 RC-5 PL005 12500

BOI 01/06/2016 RC-6 PL006 23916

CAN 03/06/2016 RC-3 PL008 3653

CAN 01/06/2016 RC-7 PL425 15705

BANK DATE CODE AMT VOUCH NO

PNB 01/06/2016 PL501 27000

PNB 01/06/2016 PL193 20000

AXIS 01/06/2016 PL025 44650

AXIS 01/06/2016 PL005 12500

CAN 03/06/2016 PL008 3653

i want to see vouch no in this coloum

BANK DATE VOUCH NO CODE AMT

PNB 01/06/2016 RC-1 PL501 27000

PNB 01/06/2016 RC-2 PL193 20000

BOI 02/06/2016 RC-1 PL194 10000 BANK DATE CODE AMT VOUCH NO

AXIS 01/06/2016 RC-3 PL124 55200 PNB 01/06/2016 PL501 27000

CANARA 03/06/2016 RC-1 PL001 48000 PNB 01/06/2016 PL193 20000

HDFC 03/06/2016 RC-2 PL005 10000 AXIS 01/06/2016 PL025 44650

AXIS 01/06/2016 RC-4 PL025 44650 AXIS 01/06/2016 PL005 12500

AXIS 01/06/2016 RC-5 PL005 12500 CANARA 03/06/2016 PL008 3653

BOI 01/06/2016 RC-6 PL006 23916

CANARA 03/06/2016 RC-3 PL008 3653 I WANT SEE TO VOUCHER NO

CANARA 01/06/2016 RC-7 PL425 15705

hello sir i need some help please help me

sir i want copy some data to sheet 1

and paste sheet 2 , b1:b50

but if b1:b50 cell have value than paste in c1:c50

thanks in advance

How many rows were loaded into the FactInternetSales table

Hello. I am trying to make a quick running counter of inventory items.

What I want to do, is be able to enter a number into a cell, and have that value transferred to different cell.

I would then like to add to that number by entering a different value into the first cell to be added & displayed in the transferred cell.

I hope I explained myself enough.

Thanks

Bill

Hello Everyone!

I am trying to make a dependent list composed of embedded objects and hyperlinks. Is this possible?

I'm having trouble figuring this one out. I have a bunch of stores that need to take training and multiple people from each one of those stores can take the training. I have a running list of all the stores and the people who work at those stores and I want excel to look in the data to find all of the same store and then bring back the most current data the training was taken. Here is my example:

Name Store Name Address City Zip Date Training Completed

Jeff My place 123 main street Austin 78745 12/5/2015

Zach My place 123 main street Austin 78745 12/6/2015

Chris My place 123 main street Austin 78745 12/7/2015

Jane His place 126 main street Austin 78748 12/8/2015

Jose His place 126 main street Austin 78748 12/9/2015

Erik My place 123 main street Austin 78745 12/10/2015

Erica My place 123 main street Austin 78745 12/11/2015

Joe Her place 130 main street Austin 78752 12/12/2015

Rob Her place 130 main street Austin 78752 12/13/2015

Moe My place 123 main street Austin 78745 12/14/2015

I would like excel to look at the data to identify all the "my place" stores and then bring back the most current training date. What formula would I use? Any help would be appreciated.

Hello - I have been asked to chart a number of conference room reservations by 2 hour intervals for the month of March. I have tried to use a Gantt chart but there are over 1048 reservations. How do I set this up to summarize identify usage during these intervals.

Hi

I am using either of the 2 following forumlas to result in a random cell from a range (A1:A15).

=INDIRECT(CONCATENATE("A",RANDBETWEEN(1,15)))

=INDEX(A1:A15,RANDBETWEEN(1,15))

However, every time I open excel, it RE-randomizes the result.

I would like a formula that gives me a random result but doesn't change every time I re-open the workbook.

Possible?

rajmammen@hotmail.com

use data validation option how can set a cell accept only divided by 6

Hello everyone, I hope you can help me...

Here is the deal:

I am consolidating data from 3 different ERP´s and I ran out of exce rows. My columns are: year, week of the year, store, item, sales in volume and sales in value (i´ve already used pivot tables to simplify the info to its minimal expression).

As I wanted to consolidate everything, one year takes more than a million rows (due to the number of items). I tried creating a pivot table form different databases but the pivot that results doesnt allow me to place the week of the year as a column. Also i though about linking slicers but it doesnt work if they are pivots created from different databases.

So here is my question...

Is there a form to link slicers in pivot tables from different sheets? or, is it possible to download an add on or something to make excel infinite?

I would really appreciate your help because im running out of time.

Best regards to everyone!

Aaah help, ive been looking at this for almost two days!!!

I have a two page spreadsheet. One page is a price list, the second is a quotation calculation sheet. I.e. sales people can drop down diferent areas of the quoation calculation sheet selcting items they want to have in the quote and the price comes out at the end. The areas with one variable is fine and i have used v look up for that but i am struggling with one formula to determine a labout price as there are two variables that need to be considered.

Type of labout, i.e. a manager or apprentice and whether it is hourly or daily! All this figures are on the first page a price list.

Labour Duration Cost Price Number Total Cost Price

Manager Hourly #VALUE!

.I want to make end to end process automatic ie for requistion to sending automatic mails to suppliers/vendors and lowest vendors will be awarded with po

On a two page student attendance record when a student is deleted I need to physically move the students below them. If I just delete the row it moves everything up including a portion of page 2's list of classes. I am trying to figure out how when one or more students are deleted the remaining students automatically fill the empty row but the class listings at the top remain and all justifications are the same.

What is a formula (MROUND?) for the following:

I need to calculate an "adjusted new price" with the following parameters:

(Column K)

if the adjusted new price is less than $0.20, round down to nearest $0.00

if the adjusted new price is greater than $0.20, but less than $0.70, round down to nearest $0.50

if the adjusted new price is greater than $0.20, round up to nearest $1.00

Distance (KM) Plain Hill

0-50 12 12.1

51-100 11.8 11.3

101-200 9.5 10.65

201-300 6.88 10.55

301-400 6.95 10.49

401-500 6.9 10.5

501-700 5.8 8.5

701-900 5.7 7.5

901-1100 5.45 5.45

1101-1300 4.64 4.95

1301-1500 4.16 4.45

1501-1700 3.82 3.95

1701 & Above 3.25 3.42

if I enter Column (O) for KM I want the right rate should come automatically according to the category pain or hill for just entering the KM that I need your help I have tried but failed

Lot Size-10000

Enter price-0.72052

Profit target-0.70416

Hedge trading entering price-0.72880

Hedge Trade profit target-0.74961

Formula--(Hedge Trade profit target*Lot Size)-(Enter Price*Lot size)=(Hedge trade Entering Price*x)-(Hedge Trade Profit Target*x)

"X" here is a variable. which we need to find out, above is the formula

I will be highly obliged if you can post a video on this, or a written formula and list of instructions would work, my working proficiency with excel is very poor, keep it as simple as possible

Please .. If I've cell that contain text and the part of this text is email address .. how can I cut this email address by function and put him in new cell

I want to convert files with cells of numbers that looks like this:

9105204422

to look like this 19910520-4422

In excel, I amortized a loan over 20 years. last year I left with (0.02). However, I should get zero. Why is that? and how to solve it?. I used rounding but I still got some other numbers in other places in excel worksheet with e.g. 0.01 etc.

Hi, I am using an Index and match function. The correct answer is provided in the formula bar however it shows as N/A in the cell.

Why is it not displaying the correct answer in the cell? (such as in the formula bar?)

I have check that the text is similar....

Hi there,

I am working in Saudi Arabia on Logistics Profile on Retail based company since 5 years.

now a got new responsibility to take care warehouse day to day transactions.

we have import and export to others parts of world, since we have weekly report to management.

Our management is interested to see how many square meters require to keep stocks; unfortunately i don't know how to resolve it.

I am looking for an Excel formula to get square meter from provided Weight.

appreciate your early response.

BR.

Hi,

I have a question on excel. I have a sheet with 400 members with their codes. I want to extract these codes for 200 selective members in another sheet as i have other information of theirs.

Kindly assist.

what is the formula for to count no. of times sum done??

for e.g. (+25+38+254+58+58) = 5

Manish Panchani

Thanks.

Please reply as soon as possible...

what is the formula for to count no. of times sum done??

for e.g. (+25+38+254+58+58) = 5

Manish Panchani

Thanks.

Please Help me For Convert Number into text such as 1800 it will be done one thousand, eight hundred. is it possible through any formula?

How to Connect my Excel Sheet in my Gmail Account & Auto Up- Date (Recover)

Hello Everyone

I have a system at work that spits out the time in the format of: 115d 20h 36m 51s

I need to convert it to either total Hours or Total Minutes.

Any assistance would be GREATLY appreciated!

Thanks,

Khalifa

Any one help me to calculate the special allowance when ctc(cost to company) =10000,and calculate basic(40% of ctc) , hra(40% of basic) , conveyance allowanceE if basic is less than 5000, value is 800,otherwise 1000, , gross, pf(12% of basic), Esic(4.75% of gross, if gross is!less than 10001 else 0), insurance(25) by using respective formulas of calculating above in excel. If gross=basic+hra+ca+sa , ctc=gross+pf+esic+insurance. Can any one help to calculate special allowance by using Excel formulas. Now we have to balance the ctc on both sides from the format ctc, basic, hra, ca, sa, gross, pf, Esic, insurance, ctc.

How can excel coding can select only 7 passed subjects from 10 subjects and finding the grading and divisions

Hi All,

Say A1 A2 and A3 cells are there,

I need this :

the value of A3 (Number) when will be equal to "+/- 1% of value of A2"(number) ...then ...A1( contains a name) will conditionally be formated to red.

Kindly help me

Regards

Sunbim31

Hi,

How to replace null cells into zero in pivot table on Google sheet?

Hi ...

It is pretty urgent and requesting you to give me a solution on the following issues im facing with Excel:

1. There are 3 columns(A, B & C) and in each column, i have five cells(rows) with some text on top of which i have added Radio buttons on all those cells. It is like when someone click on the second cell(A2's radio button), then the value '2' should display in the resultant final cell(which is 6th cell on the same column). Likewise, i have five radio buttons in each column with one final resultant cell to display the value. (Please refer the attached IMAGE) ...

This works fine with Column A but the problem is when i select the Column B's first option (i.e)., B1 radio button, the resultant final value turns as '6' in the A column's resultant cell. I want it like any radio button selected in Column A should display the result value only in column A's resultant cell and the same logic with Column B & C .... but currently, the radio buttons starting from B column till the end of C column is getting counted as continuation of column A's radio options and hence it is displaying the values from 6 to 15 that too in the column A's resultant cell.

To be precise, select any radio in column A -> resultant value should display in the final result(Score) cell of A column which is any value from 1 - 5.

select any radio in column B -> resultant value should display in the final result(Score) cell of B column which is any value from 1 - 5.

select any radio in column C -> resultant value should display in the final result(Score) cell of C column which is any value from 1 - 5.

I have tried "Right click-Protection TAB-Link cell" etc along with few other options ... but of no help ...

2. In the same sheet, i wanted to Lock only the resultant cell final(Score) values and hence protected the sheet ... but once protected, it is not allowing me to select the radio buttons/options as those are also getting protected. In other words, i want only the resultant cell to be protected and not the radio buttons ... so that my users will select any radio button and can see the resultant value in the final cell but they should not manually modify the final resultant value.

I have tried googling many of selecting Locked/Unlocked cell options along with Protection edit user range etc .. but of no use ...

Any assistance on this will be very great help please ....

Thanks,

Vijay

Hi guys I am trying to make debt control sheet were in I have see e.g

debtr Sheet

Name Jan/15 Dec/14 Nov/14

XYZ 13200 11000 7000

Sales sheet

XYZ 7400 5000 7000

I need a single formula that I can copy and paste of next cells which will calculate the days taken on particular invoice in this as the outstanding amount as of Jan/15 is 13200 which comes from 7400 +5000+800 from nov/14 that means 31days+31days and 3.4 days from need a single formula to calculate same for each month

A1 = 1000 B1=CASH OR CCVI OR CCMC OR CCAX (SELECT DROP DOWN)

X1=1000 (if selectd "CASH" in B1) formula works fine =IF(B1="CASH",A1,0)

Y1=1000 (IF selected CCVI or CCMC or CCAX)

but for above =IF(B1="CCVI",A1,0,(B1="CCMC",A1,0,IF(B1="CCAX",A1,0)))

IS NOT WORKING CAN ANY 1 HELP

this formula is working

=IF(A5="cash",B5)

but below one is not. can anybodody help????????

=IF(A5="ccvi",B5,IF(A5="ccmc",B5,IF(A5="ccax",B5)))

this formula is working

but below one is not. can anybodody help????????

=IF(A5="ccvi",B5,IF(A5="ccmc",B5,IF(A5="ccax",B5)))

there are employee grades (for example.. 11,12,13,14), for these grades rating is given to each by the managers from 0 to 5.

now we have a data base for each grades like for grade11 if the rating is 0-2.9 then 0%, 3-3.4 then 40%, 3.5-3.9,60%, 4-4.4,80%, 4.5-5, 100%.

what i need help is i need to look into each employee grade given by the manager to the data base and provide the % in other column.

can anyone help in this regard

Hi,

I have number of columns and I wan to extract all columns sheet wise. Like if I have 100 columns then I want all these 100 columns data in 100 excel sheet? Is it possible through macro or formula? Please help me for the same.

I want to get a formula that produces a message if any of the values in a given column is below say 5000. Any suggestions?

if i have a total quantity of 5000 units, with 50 units being sold each day--at what point would my units deplete? the caveat is that 30 days after a unit goes out the door, 80% come back to me to be rebuilt--so the fallout rate is 20%, how do i get to this formula---it can be by day, month, doesnt matter.

IF((o6>=15,l7*.33,0),(o6=14,l7*.32,0),(o6'13,l7*.31,0),(o6=12,l7*.30,0),(o6=11,l7*.29,0),(o6=10,l7*.28,0),(o6=9,l7*.27,0),(o6=8,l7*.26,0),(o6=7,l7*.25,0),(o6=6,l7*.24,0),(o6=5,l7*.23,0),(o6=4,l7*.22,0),(o6=3,l7*.2,0),(o6=2,l7*.2,0),(o6=1,l7*.2,0))

=if(o6>'15,l7*.33,0)(o6'14,l7*.32,0)(o6'13,l7*.31,0)(o6'12,l7*.30,0)(o6'11,l7*.29,0)(o6'10,l7*.28,0)(o6'9,l7*.27,0)(o6'8,l7*.26,0)(o6'7,l7*.25,0)(o6'6,l7*.24,0)(o6'5,l7*.23,0)(o6'4,l7*.22,0)(o6'3,l7*.2,0)(o6'2,l7*.2,0)(o6'1,l7*.2,0)

HELP

its easy

=if(o6>’15,l7*.33,0)(o6’14,l7*.32,0)(o6’13,l7*.31,0)(o6’12,l7*.30,0)(o6’11,l7*.29,0)(o6’10,l7*.28,0)(o6’9,l7*.27,0)(o6’8,l7*.26,0)(o6’7,l7*.25,0)(o6’6,l7*.24,0)(o6’5,l7*.23,0)(o6’4,l7*.22,0)(o6’3,l7*.2,0)(o6’2,l7*.2,0)(o6’1,l7*.2,0)