• 120K Members
• 3,117 Online
• 30K Conversations

## Latest Activity

Custom List Message Item

So here are the two IF functions I wish to combine together,

IF Formula 1 (Working)

=IF(\$D\$20="Left Hands",SMALL((\$F\$2:\$G\$2,\$F\$3:\$G\$3,\$F\$4:\$G\$4,\$F\$5:\$G\$5,\$F\$32:\$G\$32,\$F\$33:\$G\$33,\$F\$20:\$G\$20,\$F\$21:\$G\$21),COLUMN()-COLUMN(\$I\$2)+1),SMALL((\$F\$2:\$G\$2,\$F\$3:\$G\$3,

67 Views
4 Replies

Hope,

to produce shorter formulas, which are better to evaluate, it would be better to change the model so that you can handle the data in contiguous arrays.

Hope,

what are the possible values in D20 and D30?

I've been searching everywhere but can't find a solution to this in the right context.

I'm trying to nest an Index/Match array inside an IF statement with AND, like so...

=IF(AND(E35<>"",E35<>(INDEX(\$F\$35:\$F\$45,MATCH("*"&E35&"*",\$F\$35:\$F\$45,0)))),E35,

99 Views
5 Replies

Amir,

Checking online I've found these two formulas:

=IF(COUNT(SEARCH({"Avenue","Road"},D2)),"TRUE","FALSE") - This returns TRUE or FALSE if either word is anywhere in the cell.

=RIGHT(D2,LEN(D2)-FIND("*",SUBSTITUTE(D2," ","*",LEN(D2)-LEN(SUBSTITUTE(D2," ","")

168 Views
18 Replies

Hi, Patrick Silverwise.

1. Use Name Manager to create a name, theValue
`=\$D2`
2. Use Name Manager to create a name, LastWord
`=RIGHT(theValue,LEN(theValue)-FIND("|*|",SUBSTI`

Patrick,

`{=OR(IFERROR(LEN(A1)-SEARCH({"Avenue";"Road"},A1)+1={6;4},FALSE))}`

Hello,

I am interested in creating a daily and weekly meeting schedule that resembles a meeting calendar you can print from Outlook, Gmail, etc. I couldn't find an existing Excel template. I have found two templates that I think will work individually,

30 Views

First I get the top message even though I have macros enabled. When I disable the add-in from the developer tab I am shown this message. I have followed the help button and it has not helped. This is on Windows 10

28 Views

I have an array.  and want to search a specific column for a value greater than 0 in the array.  if i find a value greater than 0 i want to return the text entered in a different column of the array.  See below for a picture.

i want to search column lab

69 Views
6 Replies

Hi,

It could be like

`=IFNA(INDEX(<category column>, MATCH(<number>,<label column>,0)),"")`

Is there are simple formula I can use that counts the number of times a particular letter appears in a list?

For example, S = Sick, how do I calculate the number of times this appears throughout the year.

47 Views
3 Replies

Hi Jennifer,

You can use COUNTIF function as shown in the below screenshot:

Hi ,

UK user here.

I have a spreadsheet to track sales and income and we have recently registered for VAT so I want to add a drop down box to mark yes or no for VAT  purposes.

I've got the formula worked out to calculate VAT but how do I get Excel

39 Views

Update;

Columns C-G are entries for expense amounts and it will extend over 25-30 rows.

Column I

=SUM(C40:G40)*0.2 (to work out VAT amount)

Column

=SUM(C40:G40)-I40

Based on what is below. I need a formula so that if there is a NO in any of the rows b1:I1, A2= B2+(row with no). In the example A2= Yellow,Goat. I would be satisfied with 2 formulas. One that if there is a NO in the row a1=yellow. Also if there is a NO i

47 Views
2 Replies
would uou mind editing your question to clarify the row numbers.
"I need a formula so that if there is a NO in any of the rows b1:I1" Row 1?
" One that if there is a NO in ... Read More

I would like to transfer information from one sheet to another, such as.

If I have sheet 1 with A1 = Chad, B1 = 1, C1 = 2, D1 = 3; sheet 1 with A2 = John, B2 = 4, C2 = 5, D3 = 6

If I type John in A1 on sheet 2, how do I get B1, C1, and D1 to auto po

35 Views
2 Replies
Thanks for the help Willy Lau

Hello!

I just started a new job and am taking over this massive spreadsheet and have been asked to make some updates. Right now I have three separate sets of payments with payment amount, due date, payment date, etc. on a sheet. The following formula

=I

31 Views
54 Views

Not clear for which version of Excel, which locale. IMHO, better to use original

https://support.office.com/en-us/article/keyboard-shortcuts-in-excel-for-windows-1798d9d5-842a-42b8-9c99-9b7213f0040f

Hi.    I have a very simple formula: =TEXT(G3,"mmm. Dd")

and the output is unfortunately 07. פבר

The language settings (under Options/Language) are set to English.  I've tried formating the individual cells to English - no dice.  I have a new operatin

27 Views
2 Replies

Hi Gittel,

Check your local if it's one of English

or you may specify locale directly in TEXT format like

`=TEXT(G3,"[\$-409]mmm. dd")`

for US one.

Best Response confirmed by Gittel Marcus (New Contributor)

Hello,

I am trying to get it so when I put Yes in the Connected column, it will add the PSU # from column G into the box labeled Completed, put the average of PSU that has been Connected in the box labeled BSI, and add the CHL from column F that has bee

43 Views

Hi, Would there be multiple connected items i.e. multiple rows with 'Yes' in Column L? If yes, what would want in the Completed & CHL sold boxes: Sum, Count or Individual

Hi,

I have a column of data that I want to format to be bold when it is greater than 5000. But, in it I have numbers that are <1000 and written this way as well as numbers that include 0.010 I or 0.010 U. If they contain the letters or less than symbol I

33 Views

Hi, If all the values are in the same column you'll have to set-up multiple conditional formatting rules to capture all cases. You can do this by selecting the data colum

HI, I'm new to excel formulas and conditional formatting!  I'm using a spreadsheet to track new hires, and I'm trying to figure out how I can use "less footsteps" and not have to apply conditional formatting to every.single.cell. in a column.....  I'd lik

40 Views

From your screenshot, I do not know when "New Hire" and those column should be highlighted.

I think, if you are new to excel, you may not have the ideas of "Relative Reference", "Absolute Reference" and "Mixed Reference"

I am having trouble trying to come up with a function or IF statement that will input an "x" in a cell on the side indicating that the row has a colored cell in it. The colored cells already have conditional forming applied to it that tells the cell to co

64 Views
3 Replies
You may put the same formula of cknditional formating to the column m. if(that_formula,"X",""). Of course, you may need to change a bit in the formula if necessary.

I have a workbook with 4 sheets (Search, Active1, Active2 and Inactive).  Search is where the user will enter an NPI# to search the other 3 worksheets for EVERY appearance of that NPI#, presenting data from selected columns of the row that NPI# is on.  On

78 Views
8 Replies

Sorry...left off the attachment

Hi,

there is no attachment.

Hello,

I need a little nudge on a formula.

I am working on a spreadsheet that calculates day rates for my business.

Let's say a day is ten hours and the rate for that day is 1000 dollars. Each day requires the transport and setup of multiple pieces

151 Views
12 Replies

In addition to Willy's answer, see if this formula works for you.

=IF(MOD(B3,1),SUM(INT(B3)*1000,650),B3*1000)

Basically, I've devised an IF statement that checks to

Hi Randy

Are you able to upload a sample of the spreadsheet? Might be good to see what you are working with!

Cheers
Damien
`=(INT(quote)+MAX((MOD(quote,1)>0)*0.65, MOD(quote,1)))*rate`

quote can be 1,1.5, 1.7, 2, 2.1, etc.

rate should be 1000, or the absolute cell reference of the cell contains

Hi there,

I am looking to find the easiest formula solution for the following.

I wish to check if table a5:a32 contains the word "MTD", if so, it takes data from a cell in the same row from column F and then sums it in a seperate cell.

so if i had 5 M

68 Views
3 Replies

Hi Steven

If I am understanding you right, try this formula:

=SUMIF(A2:A5,"MTD",B2:B5)

Change A to your desired column where the MTD text is located.

Change B to wh

Best Response confirmed by Steven Elphick (New Contributor)
Hi,

I have two columns containing serial numbers.

I need a function which states that if the first column ends in either 616 or 796, take that column’s serial number and if it ends in neither, take the second column’s serial number.

Is there a function I can... Read More
73 Views
3 Replies

Hi Judy

You'll need a formula to do what you are proposing.

I've drafted this formula, see if it does the job for you?

=IF(OR(RIGHT(A2,3)="616",RIGHT(A2,3)="796"),A

Best Response confirmed by Judy Tran (New Contributor)

Hi, I'm wondering if there is a function/formula that I can apply to an entire column that will add entities on a row by row basis.  I want my cells in column Q, Q5 for example, to give me the sum of N5-O5-P5.  I can apply a sum formula for Q5, Q6, Q7, et

81 Views
5 Replies

Kevin,

here is one way to do it:

`=SUM(N:N)-SUM(O:O)-SUM(P:P)`

Hi, I'm using a simple search function "vert.zoeken" to retrieve a value from a second excel worksheet. If this value (in the second worksheet) is changed afterwards this must not be recalculated in the original sheet.

Is there a solution for this?

than

24 Views

Hi Paul,

If you want the values you've retrieved don't change if the original values have changed, then you have to get rid of the formulas and keep the values of these

I  have been playing around with a problem for a few days, if you could help me to derive a solution, it would be great!
 Resource 1 Resource 2 Resource 3 Resource 4 Resource 5 Resource 6 Resource 7 Resource 8
105 Views
3 Replies

Assumption:

1. your table on A1:AY501 in sheet1, where project column on column a, and resource headers from B1 to AY1.
2. project selection cell on A1 in sheet2
3. resource list

Good Morning

I am using the following formula and would like to format the results based on the results of the formula.

=IF(C2<'2-1'!B2,"u",IF(C2>'2-1'!B2,"t","v"))

When shown in Marlett font, the "u", "t", and "v" show in arrows and I need to format

68 Views
5 Replies

Alternatively you may use built-in into conditional formatting arrows icons set using formula for rules (and show icons only if desired)

Hi Tonya Troxtell, you don't need to base on the result of the arrow.  You just need three conditional formatting rule on the arrow cells to base on your formula

1. for u
`=`