I'm building a spreadsheet to track how often we respond (fire Dept) to a certain hazard level facility. I'd like to be able when I put the building number say in cell A1 that in A2 it says whether it's Low, Moderate, or High risk.
A1 cell: 1285
A2 cell: Low
do you have a table of building numbers and risks ?
A sample sheet would help here
The forum does allow for spreadsheets to be uploaded direct to the forum -
Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
Here are the instructions, found at the top of the page again
Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Sample Work Book.xlsx
See attached sample workbook
=INDEX('Building Numbers'!$B$2:$B$18,MATCH(January!F3,'Building Numbers'!$A$2:$A$18,0))
BUT
a few values are not in the table
872 RM 201
1409 RM 2533
so i see that seems to match on the numbers at the beginning
I think we need some form of explanation on what you expect excel to do
I have added the formula into column N just so you can compare against your manual result in column O
ETAF, you are amazing!!! That is exactly what we needed!!! One last question and if possible great if not this will work fine. Can I add and where would I add that if anything is column F is blank that N would be blank too instead of saying #N/A?
add an iferror()
=IFERROR(INDEX('Building Numbers'!$B$2:$B$18,MATCH(January!F3,'Building Numbers'!$A$2:$A$18,0)),"")
Thank you very much, this was awesome!!
you are welcome
Maxwell FES 2023 Run Data (Test).xls
One more formula, I hope...
I have a formula for counting how many Structural Responses that are low risk, that formula is in cell X3
in Cell X4, I need a formula that will average the time of the cells that come up as Structural Low risk.
Right now it's just two, rows 14 and 16, but there could be more later. Is there a way to average the times from Header (I) if when a row has structural selected in Header (D) and Low in Header (P)?
I hope this makes sense.
i get a lot of ref errors
but if you are after average
try
averageifs()
but i'm not following rows 14 and 15
the header in D says type and contents of the column include structural
=averageifs(I3:I52,D3:D52,"structural",P3:P52,"low")
you can use cell reference for the criteria Structural and low
Rows 14 and 16 were just ones that had the structural and Low, those were the examples, but the formula you have for the averageifs, is perfect!!! I can't thank you enough!!!
Cheers!!!! much appreciated!!!
you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks