Oct 04 2019 07:19 AM
Hello everyone,
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
Oct 04 2019 08:26 AM - edited Oct 04 2019 08:27 AM
Hello @Jorn_H,
It sounds like you could use the INDEX() and MATCH() functions. Read more here:
https://support.office.com/en-us/article/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
https://support.office.com/en-us/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a
Oct 04 2019 08:55 AM
@PReagan Thanks for replaying, I Dont know how those two formules would work on such a big scale so can you give more detail please?
Oct 04 2019 01:20 PM
Would you mind sharing a sample file of your data so that I can explain the INDEX() and MATCH() functions in further detail?
Oct 04 2019 02:00 PM - edited Oct 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!
Oct 04 2019 02:10 PM
Oct 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.
Oct 04 2019 02:27 PM
The question is - if you have 35.45, value shall be taken from column T, or from column U, or calculated proportionally?
Oct 04 2019 02:31 PM
Additionally, per your example, (unless I am mistaken) the intersection of 35 and 0.4 outputs 5752 in the table that I am looking at.
Oct 04 2019 02:31 PM
Oct 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
Oct 04 2019 03:04 PM - edited Oct 04 2019 03:06 PM
SolutionMaybe 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:
=INDEX($P$4:$Y$60,MATCH(IF(ABS(ROUND($F4-ROUNDDOWN($F4,),1))=1,ROUNDUP($F4,),ROUNDDOWN($F4,)),$O$4:$O$60,0),MATCH(IF(ABS(ROUND($F4-ROUNDDOWN($F4,),1))=1,0,ABS(ROUND($F4-ROUNDDOWN($F4,),1))),$P$3:$Y$3,0))
P.S.
I would love some suggestions to help condense this formula.
Oct 04 2019 03:09 PM
Oct 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.
=INDEX($P$4:$Y$60,VERGELIJKEN(ALS(ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))=1,AFRONDEN.NAAR.BOVEN($F4,),AFRONDEN.NAAR.BENEDEN($F4,)),$O$4:$O$60,0),VERGELIJKEN(ALS(ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))=1;0;ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))),$P$3:$Y$3,0))
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
Oct 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!
=INDEX($P$4:$Y$60,VERGELIJKEN(ALS(ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))=1,AFRONDEN.NAAR.BOVEN($F4,),AFRONDEN.NAAR.BENEDEN($F4,)),$O$4:$O$60,0),VERGELIJKEN(ALS(ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))=1,0,ABS(AFRONDEN($F4-AFRONDEN.NAAR.BENEDEN($F4,),1))),$P$3:$Y$3,0))
Oct 05 2019 12:00 AM - edited Oct 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!
Oct 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
=INDEX($P$4:$Z$60,MATCH(TRUNC(F4),$O$4:$O$60,0),(10*CEILING(ABS(ROUND(F4-TRUNC(F4),2)),0.1)+1))
I keep your formula results in green
Oct 05 2019 06:15 AM
Brilliant! Thank you!
Oct 04 2019 03:04 PM - edited Oct 04 2019 03:06 PM
SolutionMaybe 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:
=INDEX($P$4:$Y$60,MATCH(IF(ABS(ROUND($F4-ROUNDDOWN($F4,),1))=1,ROUNDUP($F4,),ROUNDDOWN($F4,)),$O$4:$O$60,0),MATCH(IF(ABS(ROUND($F4-ROUNDDOWN($F4,),1))=1,0,ABS(ROUND($F4-ROUNDDOWN($F4,),1))),$P$3:$Y$3,0))
P.S.
I would love some suggestions to help condense this formula.