10-04-2019 07:19 AM
10-04-2019 07:19 AM
I want to make an automatic cell with a formule, surrounded cell with blue. I want it so it reads the cell in front, underlined blue in the table on the right, surrounded with red. the next problem is then its bases on the number in front of the comma, first column and behind the comma top row. Note this is only half of the total table! This is the biggest problem cause it is not a small table.
Hopefully someone can help me!
thanks in advance
10-04-2019 08:26 AM - edited 10-04-2019 08:27 AM
It sounds like you could use the INDEX() and MATCH() functions. Read more here:
10-04-2019 02:00 PM - edited 10-04-2019 02:01 PM
@PReagan So what needs to happen is the first yellow column, Pmax. Needs to be the formule. The value it needs to look at it the column in front, T. With that value U need to look in the table O3;Y60. I dont know if this is possible but if it is you need to see it like this, before the comma, first example -10 is for column O. After the comma is for row 3 so in this case its just 0. If you need to chance any of this do it I just want it to work somehow!
10-04-2019 02:10 PM
10-04-2019 02:22 PM
@PReagan by each tempature there is a value for the saturated water vapor pressure, what stands above the table in Dutch. So in the table you have to top row, row 3, this stands for whats behind the comma in the column T value (ºC). On the Column O is the temperature before the comma so say the temperature is 35,4 then it needs to find 35 in column O and then in row 3 it needs to find 0,4. then where the cross each other it the value for the yellow cell. so in this case 5784. as I said if it isnt possible in this way but it is in some other way its also fine.
10-04-2019 02:31 PM
10-04-2019 02:56 PM
And what is the precision for the rounding up? Numbers in you table are results of calculations which shown with 2 digital numbers. For example, 35.4000017 will be shown as 34.40, but if formally round up decimal part it gives 35.5.
It could be round first to two digits and result rounded up to 1 digit, e.g. 35.4000017 -> 35.40 -> 35.40
10-04-2019 03:04 PM - edited 10-04-2019 03:06 PMSolution
Maybe this is what you're after. Try this rather lengthy formula out and let me know if it gives you your desired results:
In Cell G4:
I would love some suggestions to help condense this formula.
10-04-2019 03:09 PM
10-04-2019 03:52 PM
@PReagan Thanks for this formule but unfortunatly it does not work. I tried to see the evaluation from the formule but I dont know why he doesn't want to work. so can you please take a look at it if I missed something?
Note: I needed to rewrite the formule towards my language but I can assure that is right so far.
It gives the error #N/B. I found out it give it when a formule with MATCH can't find the value it's looking for, see picture. After this stap it's goes to #N/B on the position where it says AFRONDEN.NAAR.BOVEN, means ROUNDUP. So maybe there is a error in the formule
10-04-2019 11:50 PM
@PReagan Thanks for helping me, unfortunately it doesn't work I get an error at the first IF formule (ALS in Dutch). It happens at ROUNDUP and it give the error code #N/B. see also the photo's I attached. So down here is the formule rewritten in Dutch. The names are correct, maybe I miss a ) or a ; . Hopefully you can help because it looks promising!
10-05-2019 12:00 AM - edited 10-05-2019 12:19 AM
@PReagan The formule works for the positive numbers but it does not work for negative numbers so for the first 4 that are negative it's the #N/B error at the point with ROUNDUP. the other 3 work, so thanks for that already!
Edit: After looking around I found out the negative numbers had an other '-' in front so I changed those and it works now! Thank you so much!
10-05-2019 03:03 AM
Bit shorter formula could be if
1) Add helper column to the vapour pressure table for 1.0 (copy-pasting column for 0 with shifting on one row)
2) Convert texts for negative temperature to numbers applying custom number format 0;-0;-0 to show "-0"
3) Skip MATCH on columns. 0; 0.1; 0.2;... multiplied on 10 and with adding 1 already give us number of the column in the table
Finally it could be like
I keep your formula results in green
10-05-2019 12:44 PM
Thank you, but I did one mistake. At least. The point is that when we go from zero degreeC up to water or down to ice vapor pressure is different
MATCH find first in sequence row with zero. Thus if we have temperature below the zero but higher than -1, we have to jump on next from found row.
Thus I updated the formula
by adding to MATCH
It becomes longer, but I hope more correct. Result is
Attached file is corrected