• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Complicated calculations - Formulas/VBA? - How-to _?

dourpil

Member
Hi everyone!

I'm working on a file to be used as database for environmental tests.

I've reached a point where I'm facing quite a challenge.. I think I'd be able to solve it but not without spending many hours of trials/errors. Besides, what I'd come up with would probably be unefficient and heavy.

Please see attached file :)
So I have 3 worksheets:
#1 is a table where data is entered right away. It's the "every day" part of the file
#2 is a worksheet that'll populate a table in worksheet #3.
#3 is a dabatase that contains data from #2, limits (upper boundaries for results), and what I want to set up.

What's missing here is everything in M19:N36
You can see I have 2 columns: 1 alert and 1 action
I also have 18 rows that roughly correspond to a combination of tests & rooms from the databases in #1 and #3

I'll need to perform the following mathematical operation:

EXP(
LN(AVG[data]) - 0,5*LN(STDEV[data]²/AVG[data]²+1) + 2*SQRT(LN(STDEV[data]²/AVG[data]²+1)
 

Attachments

  • upload.xlsm
    66.4 KB · Views: 11
Hi,

I was not able to understand the file, pls excuse my ignorance.

meanwhile, can you populate few examples of expected output in M19:N36. As to what range in DB 1 and 3 is considered as data, etc.

Regards,
Prasad DN
 
Wow, don't apologize!
I've had issues posting my thread and had to come back later. It had saved as draft and I didn't double check. The draft wasn't complete and thus I posted the thread without all the info.. !

So here are the missing explanations:

The mathematical operation has to be done on the corresponding data, except for the values that are formatted in bold and dark red.
That's the tricky part: to create some sort of array with scattered data.

Let's only consider cell M23. It corresponds to "Room, CR3, Sas IN, Sas OUT" and test type "RCS"

the [data] that corresponds to that is :
#1: Columns R, T, U, V : cells non-empty and non-dark red
#3: Column H where column E is "RCS", column F is either "Room", "CR3", "Sas in" or "Sas out": non-dark red cells
 
Can you provide the sample solution to the data set you are considering, might help others. (not just data but an example worked out)
 
Back
Top