Update Formula so it calculates correctly

pyclen

Board Regular
Joined
Jan 17, 2022
Messages
94
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi there,

I have a spreadsheet (below, a simplified version without dropdowns and selections but accurately demonstrating what I want to achieve)

There are 3 tables, one where the tests are selected (top) and when what test is performed. The selectors are X, Y, Z for must have testing (not optional), and A, B, C for optional testing.
The second table is the table for the calculations for each timepoint, and the formula works fine w/o problem when there is a selection of X, Y, Z present, If only A, B, C, is peresent the calculation is wrong as the amount for the particular test should be zero w/o X, Y, Z.

The third table is the calculation for the optional testing, and the same formula is used but again gives the wrong result when no A, B, C is present.

The wrong results are in red in both tables. How can I modify the formula so i calculates correctly in these fields?

Any help is greatly appreciated

New Cost Sheet.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1
2Time [months]
3TestRelease1369121824364860
4adxACAC
5asxxyxy
6fdxxyxy
7ffxxyxy
8ftxxyxy
9ghxxyxy
10
11
12
13NotesTestDifficultyHourly rateSetup HoursReplicate HoursReplicates# of lotsOrientationsPackage ConfigurationsRelease1369121824364860
14adavg100122111500100100$0
15asavg10022211160010001000$0
16fdavg10032211170011001100$0
17ffavg10042211180012001200$0
18ftavg10052211190013001300$0
19ghavg100622111100014001400$0
2045006100
21
22optionalNotesTestDifficultyHourly rateSetup HoursReplicate HoursReplicates# of lotsOrientationsPackage Configurations1369121824364860
23adavg100122111900
24asavg100222111200
25fdavg100322111300
26ffavg100422111400
27ftavg100522111500
28ghavg100622111600
292900
Sheet1
Cell Formulas
RangeFormula
M13:W13M13=IF(D3="","",D3)
M14:M19M14=IF(D4="","$0",PRODUCT($F14,(SUM($G14,(PRODUCT(H14:L14))))))
N14:N19N14=IF($E4="","$0",PRODUCT($F14,(SUM($G14,PRODUCT(COUNT(SEARCH({"X","Y","Z"},$E4)),$H14:$L14)))))
O14:O19O14=IF($F4="","$0",PRODUCT($F14,(SUM($G14,PRODUCT(COUNT(SEARCH({"X","Y","Z"},$F4)),$H14:$L14)))))
P14:P19P14=IF($G4="","$0",PRODUCT(F$14,(SUM($G14,PRODUCT(COUNT(SEARCH({"X","Y","Z"},$G4)),$H14:$L14)))))
D13D13=C3
D14:D19D14=IF(C4="","",$C4)
M20:N20,N29M20=SUM(M14:M19)
C22,N22:W22,E22:L28C22=C13
D22:D28D22=C3
N23:N28N23=IF($E4="","$0",PRODUCT($F14,(SUM($G14,PRODUCT(COUNT(SEARCH({"A","B","C"},$E4)),$H14:$L14)))))
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
I think you want?

N14: =LET(c,COUNT(SEARCH({"X","Y","Z"},E4)),--IF(c,$F14*(G14+PRODUCT(c,H14:L14))))

If that's right, you can make similar adjustments to the other incorrect formulae.
 
Upvote 0

Forum statistics

Threads
1,226,538
Messages
6,191,636
Members
453,668
Latest member
Stephen_Santos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top