Home

Formulas and Functions

892 Conversations

Latest Activity

Custom List Message Item

I need help creating a formula. If the value of A2 = a number from 1 to 99 then I want the value 99 to be placed in A3. If the value of A2 is ≥100, then I want the value of A3 to be (A2*.45+54)

 

I was able to do this somewhat by using the IF formula. The

... Read More
0 View
0 Reply

Hi

I would like to find an exact match of a grape within a product name of different wines. The problem is that there are so many blends that if I use =if(isnumber(search or =if(isnumber(find the blends will not show since it will only fish for the first

... Read More
16 Views
0 Reply

Hi,

I am trying to calculate a spelling age of students... 

I need to input birth dates, date of test and a score (12 to 53)... and have a spelling age as my result.

  1. The table would need to convert birth dates to a DECIMAL AGE in YEARS on a particular da
... Read More
44 Views
4 Replies

David,

 

maybe something like this?

 

 

F5 =YEARFRAC(C5,D5)
H5 =LOOKUP(G5,DATA!$A$4:$A$45,DATA!$B$4:$B$45)
I5 =QUOTIENT(H5-F5,1)
J5 =ROUND(MOD(H5-F5,1)*12,0)

... Read More
Best Response confirmed by David DRABBLE (New Contributor)

Task: cataloging receipts from expenditures.

Have receipts with multiple items listed. The subtotal has been discounted from the original retail price by 10%. That discounted subtotal is then marked up by 7% (tax).

Would like to make only one numeric entr

... Read More
35 Views
4 Replies

Hello,

 

are you struggling with the Excel formulas to use or are you asking about the underlying maths to calculate the result? In Excel you can use the regular operator

... Read More

Adding cells to existing formula.  I add 2 more cells and the total goes to "0" instead of updating total in cell.  Not sure what I am doing wrong.  Thanks.

29 Views
2 Replies

Hello,

 

can you post the formula you are using, and also post what the cells contain? It is hard to troubleshoot a formula without seeing either formula or data.



 

Read More

Theresa,

 

there is a change from the value before to 0.  So it is updating. Maybe not the updating you expected but still updating.

It could be a format issue.

 

Read More

Need to create a chart but cannot understand how to use the excel formula ...

Please help

20 Views
1 Reply

Hello,

 

could you add a bit more detail on what you need help with? What chart?

 

You can find help about the NPV function in the Excel help. Office Support has an artic

... Read More

Hello,

Allow me to explain what I am trying to achieve.  I have 2 worksheets of data with a common field of an ID #.  In sheet 1, the ID # is in cells F2:F88.  In sheet 2, the ID # is in cells F2:F48.  I also have a start date and end date on each sheet. 

... Read More
38 Views
4 Replies
I will try this and see if it works for me. Thank you so much for your help.
Thank you for your quick response. Will this work even if there is the possibility of multiple records per ID on sheet2? Allow me to explain my data a little more clearly... Read More

Hi Lori,

 

If your end goal is to produce a list of ID's from Sheet1 that do not have a data match on Sheet2, then you can depend only on the ID in the matching process,

... Read More

Hi all :)

 

Let me explain what I an trying to achieve and hopefully someone can help me.

 

I have a excel sheet (lets call it "LocalStore") that I need to populate (rather update) data on a regular basis from a separate excel document (lets call this, "d

... Read More
61 Views
2 Replies

Hi Ryan,

 

Sorry, but without clear enough explanation, I don't think that anyone here can help you!

Please write some notes and explanations inside the workbooks and upl

... Read More
Sorry all, is there anyone who is able to assist with this?

I'm using the following formula in column I to provide me with a value ("1") for every unique instance in column G.  The formula actually works great, except for one thing.  In cell I88 it gives me a value of 1, with no other cells beyond with the same. 

... Read More
91 Views
6 Replies

My apologies - please see below, and attached, for a better explanation of my issue:

 

  1.  I have a spreadsheet with data in it starting in Cell A7 and finishing in Cell M1
... Read More

Hi Kelly,

 

Sorry, but your explanation of the issue isn't clear enough!

Anyway, I have these two formulas for you which could help you:

 

Use this if you want to hide al

... Read More

Kelly,

 

check LEN(G88).

If it is >0 then there is something in the cell.

 

 

I am not in text but general and I am in automatically calculate but my functions in excel is no longer giving me a drop down and no longer calculating

40 Views
2 Replies

This may be a result of incorrect formula/formula with errors being used in the formula of the drop down list.

 

Copy your drop down list formula to a cell and trace if i

... Read More

me too! Cant get auto sum to work and the totals have disappeared from the bottom green bar!

 

I have 5 sheets all the same, i want to add the same cell in all five sheets D4. But some of them will include #N/A. Is this possible?  

30 Views
1 Reply

Hi Colin,

 

Yes, this is possible!

Let's suppose that the five sheets are named this way:

Sheet1, Sheet2...

 

So you can put this formula in any sheet:

 

=AGGREGATE(9,6,S
... Read More

I am not in text but general and I am in automatically calculate but my functions in excel is no longer giving me a drop down and no longer calculating

29 Views
0 Reply

Another evidence proves how almost everything can be done with Excel!

 

A 3D Engine Made of Excel Formulas

A 3D Engine Made of Excel Formulas.png

 

Fantastic!

Read More
45 Views
1 Reply

This is very cool - thanks for sharing! 

Hi There,

I am trying to create a formula to find a string(s) in a column of data. The column in approx 3000 rows with different words in each cell. Some contain the strings, some do not.  If a string is found, I need  to output a value in the cell in the

... Read More
70 Views
4 Replies

Hi,

 

You can use a (Nested If) formula to do this work!

Please see it in the attached file!

 

Regards

I am trying to get a formula to check if B2 have the first 3 letter and have more than 9 characters if yes check C2 for the same logic if yes add B2&" "&C2 and check D2, if yes add D2 and check e3 and f2. the problem is some of the false statement is retu

... Read More
38 Views
2 Replies

Fernando,

 

if I understood correctly:

{=TEXTJOIN(" ",TRUE,IF(ISERROR(--LEFT(B2:F2))*(LEN(B2:F2)>9),B2:F2,""))}

 

 

 

 

Read More
Best Response confirmed by Fernando Salgueiro (Occasional Contributor)

I am trying to use a formula where a function will return a cell reference to then use in another function of the same cell.  The formula that I am trying to use is =average(vlookup(K9,A:B,2,false):vlookup(L9,A:B,2,false))

 

I am trying to get excel to lo

... Read More
35 Views
2 Replies
Check out the AVERAGEIFS function. Suppose your min and max dates are in F1 and F2:
=AVERAGEIFS($B$2:$B$100,$A$2:$A$100,"<="&$F$1,$A$2:$A$100,">="&$F$2) Read More

I am trying to compare Column A to Column B, so in C I did =A1=B1.

 

Where Column C says false, I delete the data from Column A (as B is the data I truly need).  When I have done this in the past, the formula in Column C would stay the same even when dele

... Read More
70 Views
5 Replies

Hi Amanda,

 

In general here it shall be no difference between 2010 and 2016. Could you please attach Excel file as on your screenshot instead? 

Read More

The problem I am trying to solve is... Over a single day i have two data columns, one is a staff start time and the other is staff end time. 

What I want to do is ascertain how many staff are present at a specific 15 minute time segment of the day, startin

... Read More
71 Views
9 Replies

Hi Peter,

 

For data structured like this

TimeIntervals.JPG

the formula could be

=SUMPRODUCT(($C$3:$C$6<=F3)*($D$3:$D$6>=(F3+1/24/4)))
Read More

Hi,

I have an excel sheet with 3 columns. In column A there are categories, in column B there are the different devices from each category. In column C I want to list all diveces depending of a condition. Please have a look to examble excel sheet. I know t

... Read More
103 Views
9 Replies

Andreas,

 

first delete the rows with the sub headers and fill in a column header for column A.

Then Press CTRL-SHIFT-L and filter in column A.

 

Read More

I still use @SUM() formatting for my Excel93 formulas.  I have payroll worksheets that have a line for each week of the year.  I enter the number of days worked (C1) multiplied by pay rate (D10) to get the week's gross amount (D1).  However, when I copy t

... Read More
38 Views
1 Reply

Hi there


Adjust your formula slightly by using $ within the cell reference, in your case it would be $D$10.

This is called Absolute Cell Referencing and will ensure that t

... Read More

My designated table array has multiple results. Vlookup returns only the first match it finds. Is there a way to extract more than one result from my table array? . 

26 Views
1 Reply

Hi Douglas,

 

In general yes, depends on your data structure. See, for example, https://www.extendoffice.com/documents/excel/2699-excel-vlookup-find-first-2nd-match.html

Read More

Hello...

 

Please help, I'm lost. 

 

I have a sheet with a lot of countifs and sumifs which gives a summary of performance of different people based on set criteria. 

 

I also have a drop down box so you can choose which month you want to look at - and th

... Read More
26 Views
0 Reply

Hi, maybe it is meant to be like that, but I create a link between two excel workbook and I'm missing some data. Anyone knows why?

Do I have to respect a specific format? or should know better which line and column can be text?

Capture.JPG

Read More
32 Views
4 Replies

Hi,

 

I can't reproduce that. Since your connection is on the file

Connection.JPG

which doesn't exist in my environment, I changed it on downloaded second file, from Data->Existing con

... Read More

Hello

I need a formula that tells how many times a given value exists in column "B" as long as the corresponding row value in column "A" is greater than 10.

Thank you,

101 Views
10 Replies

Hi Kleber,

 

Let say that the value you want to check is "v", and it's located in cell C2, so you can use this formula:

=COUNTIFS(A1:A13,">10",B1:B13,C2)

Please find it a

... Read More

Hi Kleber,

 

As variant that could be

=SUMPRODUCT((B:B=<your value>)*(A:A>10))

 

Hi,

 

Excel returned me an error saying I have too many arguments, can someone kindly rectify the error? I need to get the tax payable from the rate here.

 

Capture.JPG

My formula is as follow:

=if(x<=20000,0,if(and(x>20000,x<=30000),(x-20000)*0.02),if(and(x>30000,x
... Read More
47 Views
2 Replies

IMHO, instead of hardcoded nested IF or like better to use the table with tax rates and lookup on it, one of samples https://www.excel-university.com/income-tax-formula/

... Read More