getting excel to calculate correctly

pyclen · Mar 14, 2022

Hi there,
I am still working on a workbook to calculate test cost at different times, something that is very often used in the pharmaceutical industry.

I copied a dummy example using XL2bb (below) however I try to explaijn what I want.

I have a column with tests, their difficulty, what department is performing this test, a surcharge, the hourly rate for the department, then setup hours and replicate hours.
For the # of setups column I have a formula that calculates the # of setups needed based on the product of columns J --> M.
The calculation works beautifully for T0 (column N) but I need to manually adjust it from T1 (column O) and beyond.

The number of samples for T1 and beyond comes from a separate table using a countA function, I have just created a dummy table below in row 11/12 indicating number of samples at each Tx.

My problem is that I need to adjust the number of setups for T1 and beyond manually instead of having it calculate based on the product of columns J -->M times x (value in row 12 for each time)

Is there a way to do that or am I stuck with manual adjustments?

any help for this novice that is trying to make a big contribution and learn new stuff is geratly appreciated

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

2

Test

Difficulty

Group

Surcharge

hourly rate

setup hours

replicate hours

#of setups

Replicates

# of lots/strengths

Orientations

Packaging Configurations

T0

T1

t2

T3

T4

T5

t6

t7

t8

setup hours

replicate hours

3

test 1

avg

grp1

10%

5

1

$385

surcharge

Group

hr rate

Test

avg

hi

avg

hi

4

test 2

hi

grp1

0%

3

1

$650

0

grp1

100

test 1

1

1.5

0.5

1

5

test 3

avg

grp1

20%

$120

3

1

2

1

$600

0.1

grp2

150

test 2

1

2

1

1.5

6

test 4

avg

grp2

10%

$165

3

1

3

1

$990

0.2

grp3

175

test 3

3

4

1

1.5

7

test 5

hi

grp1

0%

$100

0.75

1

5

1

$450

0.3

test 4

3

5

1

2

8

0.4

test 5

0.5

0.75

0.5

0.75

9

at t1 I need to calculate the number of total samples to analyze and then adjust the number of setups based on that total.

10

that goes for every time after t1

11

stability

T0

T1

t2

T3

T4

T5

t6

t7

t8

t0 I can calculate easily

12

# of samples

1

3

2

1

13

14

Sheet1

Cell Formulas
Range	Formula
F3:F7	F3	=ROUND(IF(COUNTA($D3:$E3)<2,"",VLOOKUP($D3,group,2,0)*(1+$E3)),0)
G3:G7	G3	=IF($B3="","",INDEX(setup,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
H3:H7	H3	=IF($B3="","",INDEX(replicate,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
I3:I7	I3	=ROUNDUP(PRODUCT($J3:$M3)/10,0)
N3:N7	N3	=ROUND(PRODUCT($F3,SUM(PRODUCT($G3,$I3),PRODUCT($H3,$J3:$M3))),0)

Named Ranges
Name	Refers To	Cells
difficulty	=Sheet1!$AE$3:$AF$3	G3:H7
group	=Sheet1!$Z$4:$AA$6	F3:F7
rate	=Sheet1!$AA$4:$AA$6	F3:F7
replicate	=Sheet1!$AG$4:$AH$8	H3:H7
setup	=Sheet1!$AE$4:$AF$8	G3:G7
test	=Sheet1!$AD$4:$AD$8	G3:H7

Cells with Data Validation
Cell	Allow	Criteria
C3:C7	List	=difficulty
D3:D7	List	=group
E3:E7	List	=surcharge

jtakw · Mar 14, 2022

Hi,

Can you explain exactly what needs to be adjusted from the formula in N column to O column and on?
Also, please explain the logic of your calculation in your N column formula.

pyclen · Mar 14, 2022

Hi there,
Column N is my base, there's nothing to change.

The next column (O) needs to take into account the change in the total number of samples being analyzed based on the product of D12 and J --> M.
In other words, the value in I3 needs to be dynamic for each time point, ie it needs to be calculated for each time.
If the formula can be adjusted for the initial time as well that is an option as well.

jtakw · Mar 14, 2022

I'm afraid I'm not following, again, please explain the logic for the required calculation, step by step, for column O, then, what changes for column P ?
Where is this "time" you speak of?

Don't forget, you know everything about your spreadsheet, potential helpers are Only relying on the information You provide in order to try to help.

pyclen · Mar 14, 2022

my apologies for being not as clear as possible.

I am trying to calculate the cost at various intervals (timepoints (T0, T1, T2, T3 etc (found in Column N2 --> U2, and C11 --> L11) for samples being tested.
I made an example sheet using test 1, 2, 3, etc. as example.
Each test has a setup time and replicate time (i.e. how often the test is performed (column J).
How often I need to perform a setup (akin to a reset of an instrument) is dependent on the total number of samples I need to analyze.
At my initial timepoint T0 the number of samples is C12*J3*K3*L3*M3 (1*1*5*1*1 = 5), and since I need to perform a reset after every 10 samples I just need to do it once.
So, the cost at T0 is F3*((g3*I3)+(h3*C12*J3*K3*L3*M3) = f3*(1+2.5) = 110 * 3.5 = 385

For T1 and subsequent times (T2/3/4 etc) that formula needs to be dynamic since D12, E12, F12 etc. may not be 1.
For T1 the value is D12*J3*K3*L3*M3 (3*1*5*1*1 = 15), therefore I need to perform 2 resets since 15>10 however the above formula that I used for T0 needs to be manually adjusted to account for the second reset.
F3*((g3*I3)+(h3*D12*J3*K3*L3*M3) = f3*(2+(0.5*15) = f3*(2+7.5) = 110 * 9.5 = 1045.

The same goes for subsequent times (T2/3/4...), how can I account for the changing value in D12/E12/F12 and resets I need to perform, i.e. make I3 dynamic

bebo021999 · Mar 14, 2022

Not 100% understanding, but below formula returns what you expected for T0 & T1:

sumeven.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

1

2

Test

Difficulty

Group

Surcharge

hourly rate

setup hours

replicate hours

#of setups

Replicates

# of lots/strengths

Orientations

Packaging Configurations

T0

T1

t2

T3

T4

T5

t6

t7

t8

setup hours

replicate hours

3

test 1

avg

grp1

0.1

5

1

surcharge

Group

hr rate

Test

avg

hi

avg

hi

4

test 2

hi

grp1

0

3

1

0

grp1

100

test 1

1

1.5

0.5

1

5

test 3

avg

grp1

0.2

120

3

1

2

1

0.1

grp2

150

test 2

1

2

1

1.5

6

test 4

avg

grp2

0.1

165

3

1

3

1

0.2

grp3

175

test 3

3

4

1

1.5

7

test 5

hi

grp1

0

100

0.75

1

5

1

0.3

test 4

3

5

1

2

8

0.4

test 5

0.5

0.75

0.5

0.75

9

10

11

stability

T0

T1

t2

T3

T4

T5

t6

t7

t8

12

# of samples

1

3

2

1

Sheet3

Cell Formulas
Range	Formula
F3:F7	F3	=ROUND(IF(COUNTA($D3:$E3)<2,"",VLOOKUP($D3,group,2,0)*(1+$E3)),0)
G3:G7	G3	=IF($B3="","",INDEX(setup,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
H3:H7	H3	=IF($B3="","",INDEX(replicate,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
I3:I7	I3	=ROUNDUP(PRODUCT($J3:$M3)/10,0)
N3:V7	N3	=$F3($G3ROUNDUP(HLOOKUP(N$2,$C$11:$K$12,2,0)$J3$K3$L3$M3/10,0)+$H3HLOOKUP(N$2,$C$11:$K$12,2,0)$J3$K3$L3*$M3)

Named Ranges
Name	Refers To	Cells
difficulty	=Sheet3!$AE$3:$AF$3	G3:H7
group	=Sheet3!$Z$4:$AA$6	F3:F7
rate	=Sheet3!$AA$4:$AA$6	F3:F7
replicate	=Sheet3!$AG$4:$AH$8	H3:H7
setup	=Sheet3!$AE$4:$AF$8	G3:G7
test	=Sheet3!$AD$4:$AD$8	G3:H7

RoryA · Mar 15, 2022

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

https://www.myonlinetraininghub.com/excel-forum/excel/getting-excel-to-calculate-correctly

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

pyclen · Mar 15, 2022

bebo021999 said:
Not 100% understanding, but below formula returns what you expected for T0 & T1:

sumeven.xlsm
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH
1
2 Test Difficulty Group Surcharge hourly rate setup hours replicate hours #of setups Replicates # of lots/strengths Orientations Packaging Configurations T0 T1 t2 T3 T4 T5 t6 t7 t8 setup hours replicate hours
3 test 1 avg grp1 0.1 110 1 0.5 1 5 1 1 1 385 1045 1045 1045 660 660 660 385 385 surcharge Group hr rate Test avg hi avg hi
4 test 2 hi grp1 0 100 2 1.5 1 3 1 1 1 650 1550 1550 1550 1100 1100 1100 650 650 0 grp1 100 test 1 1 1.5 0.5 1
5 test 3 avg grp1 0.2 120 3 1 1 2 1 1 1 600 1080 1080 1080 840 840 840 600 600 0.1 grp2 150 test 2 1 2 1 1.5
6 test 4 avg grp2 0.1 165 3 1 1 3 1 1 1 990 1980 1980 1980 1485 1485 1485 990 990 0.2 grp3 175 test 3 3 4 1 1.5
7 test 5 hi grp1 0 100 0.75 0.75 1 5 1 1 1 450 1275 1275 1275 825 825 825 450 450 0.3 test 4 3 5 1 2
8 0.4 test 5 0.5 0.75 0.5 0.75
9
10
11 stability T0 T1 t2 T3 T4 T5 t6 t7 t8
12 # of samples 1 3 3 3 2 2 2 1 1
Sheet3
Cell Formulas
Range Formula
F3:F7 F3 =ROUND(IF(COUNTA($D3:$E3)<2,"",VLOOKUP($D3,group,2,0)*(1+$E3)),0)
G3:G7 G3 =IF($B3="","",INDEX(setup,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
H3:H7 H3 =IF($B3="","",INDEX(replicate,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
I3:I7 I3 =ROUNDUP(PRODUCT($J3:$M3)/10,0)
N3:V7 N3 =$F3*($G3*ROUNDUP(HLOOKUP(N$2,$C$11:$K$12,2,0)*$J3*$K3*$L3*$M3/10,0)+$H3*HLOOKUP(N$2,$C$11:$K$12,2,0)*$J3*$K3*$L3*$M3)
Named Ranges
Name Refers To Cells
difficulty =Sheet3!$AE$3:$AF$3 G3:H7
group =Sheet3!$Z$4:$AA$6 F3:F7
rate =Sheet3!$AA$4:$AA$6 F3:F7
replicate =Sheet3!$AG$4:$AH$8 H3:H7
setup =Sheet3!$AE$4:$AF$8 G3:G7
test =Sheet3!$AD$4:$AD$8 G3:H7

THank you very much for your help, and I believe that is what I am trying to do

pyclen · Mar 22, 2022

Ok, I am grateful for the help received so far however the above solution only works on a single row (here row 12), i.e the calculation is correct in cell N4:N8, nowhere else as the test is different as well as the number of replicates.
The formulas in N4:N8 need to be modified so that the referenced test is correct as well as the timepoint in C8:K8

Again, any help is appreciated,
Thank you

Example.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

2

setup hours

replicate hours

3

Test

Difficulty

Group

Surcharge

hourly rate

setup hours

replicate hours

#of setups

Replicates

# of lots/strengths

Orientations

Packaging Configurations

T0

T1

t2

T3

T4

T5

t6

t7

t8

surcharge

Group

hr rate

Test

avg

hi

avg

hi

4

test 1

avg

grp1

0%

0

grp1

100

test 1

1

5

test 2

avg

grp1

0%

0.1

grp2

150

test 2

2

6

test 3

avg

grp1

0%

0.2

grp3

175

test 3

3

7

test 4

avg

grp1

0%

0.3

test 4

4

8

test 5

avg

grp1

0%

0.4

test 5

5

9

10

11

stability timepoints

T0

T1

t2

T3

T4

T5

t6

t7

t8

12

test 1

1

2

3

4

5

6

7

8

9

13

test 2

1

2

3

4

5

6

7

8

9

14

test 3

1

2

3

4

5

6

7

8

9

15

test 4

1

2

3

4

5

6

7

8

9

16

test 5

1

2

3

4

5

6

7

8

9

17

18

19

Sheet1

Cell Formulas
Range	Formula
F4:F8	F4	=ROUND(IF(COUNTA($D4:$E4)<2,"",VLOOKUP($D4,$AB$4:$AC$6,2,0)*(1+$E4)),0)
G4:G8	G4	=IF($B4="","",INDEX(setup,MATCH($B4,test,0),MATCH($C4,Difficulty,0)))
H4:H8	H4	=IF($B4="","",INDEX(replicate,MATCH($B4,test,0),MATCH($C4,Difficulty,0)))
I4:I8	I4	=ROUNDUP(PRODUCT($J4:$M4)/10,0)
N4:V8	N4	=$F4($G4ROUNDUP(HLOOKUP(N$3,$C$11:$K$16,2,0)$J4$K4$L4$M4/10,0)+$H4HLOOKUP(N$3,$C$11:$K$16,2,0)$J4$K4$L4*$M4)
B12:B16	B12	=B4

Named Ranges
Name	Refers To	Cells
Difficulty	=Sheet1!$AG$3:$AH$3	G4:H8
group	=Sheet1!$AB$4:$AB$6	F4:F8
rate	=Sheet1!$AC$4:$AC$6	F4:F8
replicate	=Sheet1!$AI$4:$AJ$8	H4:H8
setup	=Sheet1!$AG$4:$AH$8	G4:G8
test	=Sheet1!$AF$4:$AF$8	G4:H8

Cells with Data Validation
Cell	Allow	Criteria
B4:B8	List	=test
C4:C8	List	=Difficulty
D4:D8	List	=group
E4:E8	List	=surcharge

getting excel to calculate correctly

pyclen

Board Regular

Excel Facts

jtakw

Well-known Member

pyclen

Board Regular

jtakw

Well-known Member

pyclen

Board Regular

bebo021999

Well-known Member

RoryA

MrExcel MVP, Moderator

pyclen

Board Regular

pyclen

Board Regular

Similar threads

Forum statistics

Share this page

getting excel to calculate correctly

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

MrExcel MVP, Moderator

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock