If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Add if a condition is might . Help please a real novice

Thread starter jwalkerack
Start date Jul 29, 2013
Tags

addif look up sumif

J

jwalkerack

Board Regular

Jul 29, 2013

#1

Hello everyone I am going to try and put into words what it is that I would like to do. Hope it makes sense? I will try lol. Although I’m not sure how to categorise what it is I want. But here goes

I have a range of pressure reading numbers. That range from 0 to 6000. Because Pressure is influenced by temperature a correction has to be to the readings. For example if the pressure reading was 3330 and it was at 150 degrees then (-20) pressure would have to be added.

I have a table that summarises what corrections need to be made. As the table below. This are controlled by firstly the Pressure reading and then the temperature. This controls which value is to be used to correct.

[TABLE="class: MsoNormalTable, width: 170"]
<tbody>[TR]
[TD="width: 64"] Pressure
[/TD]
[TD="width: 84"] Temperture [/TD]
[TD="width: 79"] Correction [/TD]
[/TR]
[TR]
[TD="width: 64"]

1000

[/TD]
[TD="width: 84"]

100

[/TD]
[TD="width: 79"]

10

[/TD]
[/TR]
[TR]
[TD="width: 64"]

1000

[/TD]
[TD="width: 84"]

125

[/TD]
[TD="width: 79"]

13

[/TD]
[/TR]
[TR]
[TD="width: 64"]

1000

[/TD]
[TD="width: 84"]

150

[/TD]
[TD="width: 79"]

15

[/TD]
[/TR]
[TR]
[TD="width: 64"]

1000

[/TD]
[TD="width: 84"]

175

[/TD]
[TD="width: 79"]

17

[/TD]
[/TR]
[TR]
[TD="width: 64"]

2000

[/TD]
[TD="width: 84"]

100

[/TD]
[TD="width: 79"]

12

[/TD]
[/TR]
[TR]
[TD="width: 64"]

2000

[/TD]
[TD="width: 84"]

125

[/TD]
[TD="width: 79"]

16

[/TD]
[/TR]
[TR]
[TD="width: 64"]

2000

[/TD]
[TD="width: 84"]

150

[/TD]
[TD="width: 79"]

19

[/TD]
[/TR]
[TR]
[TD="width: 64"]

2000

[/TD]
[TD="width: 84"]

175

[/TD]
[TD="width: 79"]

25

[/TD]
[/TR]
[TR]
[TD="width: 64"]

3000

[/TD]
[TD="width: 84"]

100

[/TD]
[TD="width: 79"]

35

[/TD]
[/TR]
[TR]
[TD="width: 64"]

3000

[/TD]
[TD="width: 84"]

125

[/TD]
[TD="width: 79"]

32

[/TD]
[/TR]
[TR]
[TD="width: 64"]

3000

[/TD]
[TD="width: 84"]

150

[/TD]
[TD="width: 79"]

67

[/TD]
[/TR]
[TR]
[TD="width: 64"]

3000

[/TD]
[TD="width: 84"]

175

[/TD]
[TD="width: 79"]

23

[/TD]
[/TR]
[TR]
[TD="width: 64"]

4000

[/TD]
[TD="width: 84"]

100

[/TD]
[TD="width: 79"]

59

[/TD]
[/TR]
[TR]
[TD="width: 64"]

4000

[/TD]
[TD="width: 84"]

125

[/TD]
[TD="width: 79"]

35

[/TD]
[/TR]
[TR]
[TD="width: 64"]

4000

[/TD]
[TD="width: 84"]

150

[/TD]
[TD="width: 79"]

50

[/TD]
[/TR]
[TR]
[TD="width: 64"]

4000

[/TD]
[TD="width: 84"]

175

[/TD]
[TD="width: 79"]

24

[/TD]
[/TR]
</tbody>[/TABLE]

So as I am entering in my pressure readings I would like to be able to automatically populate the correction in another cell
For example if I had a pressure reading of 3300 at 140 degrees .The cell would know that it is in the 3000 range. The next step would be to know that 140 is closest to 150 and it would add 67 . The problem is that pressure readings are on a scale of 1000 so they are not an exact number. Excel needs to understand that its a range of 0 - 999 , 1000 -1999 2000- 2999 and So on . The same problem with the temperatures, Excel needs pick the number that is closest to the reported temperature. 144 , would be 150 , 99 would be 100 and So on

Does anyone have any ideas where would be a good place to start. I know it is going to be a really long equation that I put in and a bit hard to describe but any help would be great.

Thanks for your time

Jack

Excel Facts

Excel Can Read to You

Click here to reveal answer

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Sort by date Sort by votes

shg

MrExcel MVP

Jul 29, 2013

#2

Can you use a user-defined function?

Code:

      ----A----- -B-- --C-- -D-- -E-- -------------F-------------
  1   Temp\Press 1000  2000 3000 4000                            
  2          100   10    12   35   59                            
  3          125   13    16   32   35                            
  4          150   15    19   67   50                            
  5          175   17    25   23   24                            
  6                                                              
  7                   Press Temp Corr                            
  8                    3300  140 50.3 E8: =BiLInterp(C8,D8,A1:E5)

The code is in the file xlInterpolators.bas at https://app.box.com/s/s0m5gs96jd3stuqa7tpu

Copy it to your desktop (or wherever), then

o Open Excel and your workbook and arrange the data in table form as shown above.

o Do Alt + F11 to open the VBE

o Look for the Project Explorer window. If you don't see it, do Ctrl+R

o In that window, you'll see something like VBA Project (NameOfYourWorkbook)

o Drag the .bas file from the directory onto the project

o Use the formula as shown.

You will need to save the resulting workbook as an xlsm file and enable macros when you open it thereafter.

Upvote 0

C

circledchicken

Well-known Member

Jul 29, 2013

#3

Hi Jack,

Can you provide a little more detail on the temperature please?

For example, for pressure 3300, what should the correction be for a temperature of:

112.5?
101?

Upvote 0

J

jwalkerack

Board Regular

Jul 29, 2013

#4

Hi guys thanks for your help

The correction is loosely based on the temperature. So in in the example 112.5 and 101 the temperature value closest to this would be used . In this situation it would be 100 degrees . So this is also a problem as the code , function needs to be able to pick the closest value to the one that is entered

Upvote 0

J

jwalkerack

Board Regular

Jul 29, 2013

#5

Thanks for the help guys ,

So in the below examples i would like it to automactically pick the value that is closest
112.5? so this would use the 100 degree correction
101 , this would also use the 100 degree correction

Upvote 0

C

circledchicken

Well-known Member

Jul 29, 2013

#6

Ok, in that case, with your setup like this:

Excel 2013

A

B

C

D

E

F

G

1

Pressure

Temperture

Correction

Pressure

Temperature

Correction

2

1000

100

10

3300

112.5

35

3

1000

125

13

4

1000

150

15

5

1000

175

17

6

2000

100

12

7

2000

125

16

8

2000

150

19

9

2000

175

25

10

3000

100

35

11

3000

125

32

12

3000

150

67

13

3000

175

23

14

4000

100

59

15

4000

125

35

16

4000

150

50

17

4000

175

24

Sheet1

Try this in G2 (note you need to press CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula):

Code:

=INDEX(
    C$2:C$17,
    MATCH(
      MIN(IF(A$2:A$17 = ROUNDDOWN(E2, -3), ABS(B$2:B$17 - F2))),
      IF(A$2:A$17 = ROUNDDOWN(E2, -3), ABS(B$2:B$17 - F2)),
      0))

This assumes a middle temperature value (e.g. 112.5) looks down for its closest match as per your post #4.

Upvote 0

J

jwalkerack

Board Regular

Jul 29, 2013

#7

That is great that is exactly what i am looking for i have to be tweak it a little to my data set but that is brilliant thank you very much

Upvote 0

C

circledchicken

Well-known Member

Jul 29, 2013

#8

You're welcome - thanks for the feedback.

Upvote 0

J

jwalkerack

Board Regular

Jul 29, 2013

#9

Hi Mate, this is actually awesome . It would make total sense , but i think the values that are being used are just the ones that are closest to the temperature. But for an exact figure this works out great

Upvote 0

shg

MrExcel MVP

Jul 29, 2013

#10

You're welcome. BTW,

Code:

150   15    19   67   50

The 67 looks anomalous in that data.

Upvote 0

You must log in or register to reply here.

Similar threads

A

Question

After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"

Replies: 1

Views: 392

Feb 22, 2024

Ark68

A

A

Question

Auto Update Sheets in Scheduler file

Replies: 6

Views: 545

Feb 17, 2024

anuradhagrewal

A

R

Question

VBA to add to data model when creating Power Query tables`

Replies: 3

Views: 577

Jul 18, 2024

Raddle

R

I

Question

Advice to remove old code for a new code

Replies: 2

Views: 202

Aug 14, 2024

ipbr21054

I

S

Question

VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns

Replies: 1

Views: 114

Oct 9, 2024

sijpie

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,223,896

Messages: 6,175,264

Members: 452,627

Latest member: KitkatToby

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top