I am trying to solve the following.

For cell B3 I have attempted to do so using a nested IF solution, but I am not sure that that is correct? For cell I3 I do not really have a solution!

Cell B3 should display the result based on data contained in cells C3, E3, G3, I3 and K3. Note cell C3 has a LET formula that cannot be altered. Cells E3, G3 and K3 are fixed values. Cell I3 is variable data according to description below.

The formula for B3 should solve:

IF C3<=G3 THEN B3 = C3 or

IF C3>G3 and <=K3 THEN B3 = C3–I3 or

IF C3>K3 THEN B3 = K3

The conditions stated cannot occur concurrently.

The formula for I3 should solve:

IF C3-E3>3 then I3 = 50% of the difference of C3-I3 above 3 ignoring the first 0.1 of difference, e.g. only displays 0.2 or higher of difference. The maximum difference is in the range of 0.2 to 1.8 in 0.1 increments. The cell should return BLANK if the result is 3 or less.

Thanks F

# Tag: Worksheet

## google sheets – Using Cell Value as a Worksheet Name in Formula

I wanted to use a cell’s content as a sheet name, i.e.

A1 |
A2 |
A3 |
A4 |
---|---|---|---|

South | North | East | West |

and use the cell values (South, North, East, West) to reference cells from sheets called South, North, East, West like this:

=South!A1

=North!A1

etc. . .

I wanted to use this to count all instances of TRUE in column A. My current implementation was inspired by a solution for Microsoft Excel and uses the *INDIRECT()* function, but it doesn’t seem to work for Google Sheets:

=ARRAYFORMULA(SUM(IF(INDIRECT(A1)!$A:$A = TRUE, 1, 0)))

## worksheet function – MS Excel – Locate like terms in cells

I am trying to locate and identify neighboring lines with a matching word.

For example:

| –Col A– | -Col B- |

| That Car!! | (blank) |

| Blue Car$ | (blank) |

| Ponies^!_ | (blank) |

| Car Sales | (blank) |

| Detroit__ | (blank) |

| Banana^ | (blank) |

“That Car!!” and “Blue Car$” are neighboring cells both containing the word “car.” I cannot figure out how to write a =formula() in Col B to identify matching words. All the formula needs to do is identify the duplicate word (not case sensitive) and output the word “BLANK” for the second line. Remove Duplicates will do the rest.

The line containing “Car Sales” will not be identified.

How can I do this?

## worksheet function – Expand Formula in Excel

I have added new columns to my spreadsheet and have gone from 60 columns (H11:BO11) to 500 (H11:SM11) but my formula has not automatically continued as it involves multiple cells. The only way I can expand it is to type in each new cell to the formula. Does anyone know an easier way?

This is the current formula from H11 to BO11:

=IF(D11=””,0(IF(H11=$H$10,1,0)+IF(I11=$I$10,1,0)+IF(J11=$J$10,1,0)+IF(K11=$K$10,1,0)+IF(L11=$L$10,1,0)+IF(M11=$M$10,1,0)+IF(N11=$N$10,1,0)+IF(O11=$O$10,1,0)+IF(P11=$P$10,1,0)+IF(Q11=$Q$10,1,0)+IF(R11=$R$10,1,0)+IF(S11=$S$10,1,0)+IF(T11=$T$10,1,0)+IF(U11=$U$10,1,0)+IF(V11=$V$10,1,0)+IF(W11=$W$10,1,0)+IF(X11=$X$10,1,0)+IF(Y11=$Y$10,1,0)+IF(Z11=$Z$10,1,0)+IF(AA11=$AA$10,1,0)+IF(AB11=$AB$10,1,0)+IF(AC11=$AC$10,1,0)+IF(AD11=$AD$10,1,0)+IF(AE11=$AE$10,1,0)+IF(AF11=$AF$10,1,0)+IF(AG11=$AG$10,1,0)+IF(AH11=$AH$10,1,0)+IF(AI11=$AI$10,1,0)+IF(AJ11=$AJ$10,1,0)+IF(AK11=$AK$10,1,0)+IF(AL11=$AL$10,1,0)+IF(AM11=$AM$10,1,0)+IF(AN11=$AN$10,1,0)+IF(AO11=$AO$10,1,0)+IF(AP11=$AP$10,1,0)+IF(AQ11=$AQ$10,1,0)+IF(AR11=$AR$10,1,0)+IF(AS11=$AS$10,1,0)+IF(AT11=$AT$10,1,0)+IF(AU11=$AU$10,1,0)+IF(AV11=$AV$10,1,0)+IF(AW11=$AW$10,1,0)+IF(AX11=$AX$10,1,0)+IF(AY11=$AY$10,1,0)+IF(AZ11=$AZ$10,1,0)+IF(BA11=$BA$10,1,0)+IF(BB11=$BB$10,1,0)+IF(BC11=$BC$10,1,0)+IF(BD11=$BD$10,1,0)+IF(BE11=$BE$10,1,0)+IF(BF11=$BF$10,1,0)+IF(BG11=$BG$10,1,0)+IF(BH11=$BH$10,1,0)+IF(BI11=$BI$10,1,0)+IF(BJ11=$BJ$10,1,0)+IF(BK11=$BK$10,1,0)+IF(BL11=$BL$10,1,0)+IF(BM11=$BM$10,1,0)+IF(BN11=$BN$10,1,0)+IF(BO11=$BO$10,1,0)))

TIA!!!

## worksheet function – Excel Formula To Get Last Non Zero or Not Blank Value in Column And Return Value

I already saw a few related questions on here about finding the last item but my question is a bit different because I’m trying to find the last not blank or non zero value in a column and either return that value or return 0 if there is nothing there. I have written a formula that works for me except for the very first row in the column and returns an error because it can’t find a value. I tried to change the formula to return a 0 if it doesn’t find anything but apparently wound up making the problem worse. Here is my current attempt at a formula:

=IF(B12=0,0,IF(LOOKUP(2,1/($B$10:B11<>0),$B$10:B11)=0, 0, B12 – IF(ISBLANK(LOOKUP(2,1/($B$10:B11<>0),$B$10:B11)), 0, LOOKUP(2,1/($B$10:B11<>0),$B$10:B11))))

## Excel: Writing a ‘report’ within a worksheet

## Summary

I want to have a specific worksheet within a workbook display a set of data from another worksheet within the same workbook – like a kind of report – in a specific order, for re-use/population of other systems.

A PivotTable isn’t an option, as i’m not wanting a summary of the data – i’m wanting to see all of the actual data in a single row of columns like any other workbook or table.

Is this possible, and how?

## Explanation

I have a multi-user workbook, which has a worksheet called CompletionData (and a data table for the information of the same name).

Not all of my users are tech savvy, so I need to achieve this within Excel for their simplicity.

My intent is to have a separate worksheet (let’s call that new worksheet BillingData, within the same workbook) that anyone can go to and get the data needed in the needed format and order, for population into another place (sadly, another workbook).

Some columns within BillingData will be populated with VLOOKUP’s from elsewhere, and that i’m comfortable with that issue to not need assistance; as will filters on the BillingData worksheet so they can get straight to what they need to action (hence the skipped column numbers in the table below).

Auto-population of the other workbook is not an option here, for reasons of access.

In order to populate into another place, I need to:

- render the information on the BillingData worksheet in a specific order, and
- as new information is entered on CompletionData, have that information flow through without manual intervention to BillingData

The mapping looks a little something like this:

Columns in CompletionData | Intended Column order in BillingData |
---|---|

A | |

B | B |

C | C |

D | E |

E | |

F | |

G | A (which will be changed using a CONCATENATE in this worksheet to format a specific way) |

H | |

I | J |

J | |

K | |

L |

## worksheet function – Excel formula/solution for grading multiple choice questions with multiple answers

I have an Excel sheet where I input student test answers like so:

- A
- B
- A,B
- C,D

and compare these answers to the correct answers:

- A
- A
- B
- C,D,E

I know how to use an IF function to output ‘CORRECT’ in a cell if the student answer is correct (e.g. answer 1) and otherwise spit out ‘INCORRECT’.

What I can’t seem to do is find a formula which will spit out ‘CORRECT’ if correct (1); ‘INCORRECT’ if wrong (2); ‘MISTAKE’ if the student got one of the answers correct, but at the same time answered another choice incorrectly (3); and ‘MISSED’ if the student answered correctly but missed an answer (4).

Is there any way to do this? I’ve tried using wildcards and countifs but it’s beyond my Excel level.

Thanks in advance for any help!

## worksheet function – Excel data query

I’m trying to build out a budget monitoring spreadsheet. I want to pull sum of entries by category and based on the month. The data is in rows as follows and will grow each month as I download our spend.

Date Category Amount

03-15-21 Auto 500.00

02-12-21 House 375.00

Ultimately I’ll have it displayed with months across columns and categories in rows so we can monitor our budget.

How can I pull out the data by category and month?

I’ve looked at sumifs, sumproduct, index match, haven’t found a solution. I appreciate any guidance.

## worksheet function – How to create a Destination file that is linked to a Source on Excel offline?

I am struggling with an excel feature regarding creating a **Source** and *Destination* workbook.

My **Source** data comes from the web using which I must create dashboards on Power BI. But before that, I make few changes to the **Source** data such that it is suitable for Power BI and represents certain information.

Since I have to make these changes to the **Source** data , I cannot directly link the data for automatic updates on Power BI. (Using from Web option)

And also my Source data is updated with new rows every week.

So the idea here is to create a *Destination* excel sheet with all necessary columns to connect on Power BI. And then download the **Source** file and create a link.

With the help of this link, every week when I download new **Source** data report, and refresh my *Destination* file, it should get updated with the new rows.

And as the *Destination* file will be linked to Power BI, my dashboards will also be updated with new information.

How can I make this happen? I am not excel expert and really need help!!

PS I am using office 365, Excel

## worksheet function – Selecting the first row in excel where a column value equals a certain value

I have a excel sheet which displays the status of various products on my website throughout the day, along with the timestamp of the status as so:

```
| Timestamp | Product 1 | Product 2 | Product 3 | Product 4 |
|-------------------------|-----------|-----------|-----------|-----------|
| 25/03/2021 08:15:52.020 | 999 | | 0 | 999 |
| 25/03/2021 08:15:53.000 | 0 | 101 | 0 | |
| 25/03/2021 08:15:53.070 | 0 | 0 | | 0 |
```

A status of 0 means a product is available, while blank cells/other numbers indicate a product is unavailable.

For each Product I would like to get the first timestamp for which the product is available (i.e. when the value in the cell is 0 for the first time, not non-0 or blank). The end result being something like this:

```
| | Timestamp |
|-----------|-------------------------|
| Product 1 | 25/03/2021 08:15:53.000 |
| Product 2 | 25/03/2021 08:15:53.070 |
| Product 3 | 25/03/2021 08:15:52.020 |
| Product 4 | 25/03/2021 08:15:53.070 |
```

Is this possible to do using pure excel functions? Or would I need to use some Python/Pandas to get my desired table? Thank you!!