• 120K Members
• 2,991 Online
• 30K 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

8 Views

Hi Norman,

That could be

`=IF(A2>=100,A2*0.45+54,IF(A2>0,A2,0))`

Formula in Excel never returns blank, it'll be zero in that case. Or you may return "" with one more nes

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

23 Views

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
45 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)

Best Response confirmed by David DRABBLE (New Contributor)

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

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

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.

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.

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

20 Views

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

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.

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,

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

62 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

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.

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

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

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

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

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`

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

30 Views

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

A 3D Engine Made of Excel Formulas

Fantastic!

45 Views

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

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

38 Views
2 Replies

Fernando,

if I understood correctly:

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

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

36 Views
2 Replies
Check out the AVERAGEIFS function. Suppose your min and max dates are in F1 and F2:

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

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?

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

71 Views
9 Replies

Hi Peter,

For data structured like this

the formula could be

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

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

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.

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

38 Views

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

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

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

Hello...

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

27 Views

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?

32 Views
4 Replies

Hi,

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

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

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)`

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.

My formula is as follow:

`=if(x<=20000,0,if(and(x>20000,x<=30000),(x-20000)*0.02),if(and(x>30000,x`
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/