'IF' query with two outcomes referencing another formula.

Pr0ximitea

New Member
Joined
Apr 27, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am putting together a spreadsheet that needs to be flexible with numbers but quick and easy to amend. I'm a bit poor at explaining so hopefully this will make sense!

I have a Capacity Cell (D5) - context is products that can go down a line in an hour.

Linked to this cell are 9 rows (simple =D$2 formula), each one representing an hour of the day, with two containing adjustments for breaktimes (/3 for 20 mins break, /2 for 30 mins break).

However, where certain products go down the line, the capacity decreases by 60 (e.g. 180 is the capacity per hour normally, reduces down to 120 when a certain product is being produced).

Therefore, what I want to do is have a cell reference the Capacity in D5, however if the word 'Silverside' appears in the Description box beside it, it is reduced by 60.

Below is the reference I attempted to use (also didn't work) - but the problem with this is that it would be static with 120 or 180 - I want it to reference what is in cell D5 on a normal basis, however when the word 'Silverside' appears in the H23:H28 section, I want it to deduct -60 from that value.

=IF(H23:H28="Silverside","120",IF(H23:H28<>"Silverside","180"))

Am I being overambitious or is this potentially a formula that someone could please share with me? Again, apologies for my poor explaining skills, and thank you in advance for any help!
 

Attachments

  • Screenshot 2023-04-27 134859.png
    Screenshot 2023-04-27 134859.png
    6.3 KB · Views: 9

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
MrExcelPlayground16.xlsx
CDEFGH
5180
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23120Fred
24George
25Silverside
26Biff
27Mary
28Sally
Sheet26
Cell Formulas
RangeFormula
C23C23=IF(SUM(--(H23:H28="Silverside"))>0,D5-60,D5)
 
Upvote 0
Solution
MrExcelPlayground16.xlsx
CDEFGH
5180
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23120Fred
24George
25Silverside
26Biff
27Mary
28Sally
Sheet26
Cell Formulas
RangeFormula
C23C23=IF(SUM(--(H23:H28="Silverside"))>0,D5-60,D5)
Thank you so much! This is exactly what I needed - you're a star!

Is there any way that I can have it pick up anything with the word Silverside in it, as opposed to have to strictly just say 'Silverside'? For example, I have a product called 'Fat Off Silverside' but it won't be picked up by that formula as it's not exact?

Thank you again :)
 
Upvote 0
There sure is a way - using search and looking for a number result instead of an NA result:

MrExcelPlayground16.xlsx
CDEFGH
5180
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23120Fred
24George
25Silly Silverside Goose
26John Silverside
27Mary
28Sally
Sheet26
Cell Formulas
RangeFormula
C23C23=IF(--OR(ISNUMBER(SEARCH("Silverside",H23:H28)))>0,D5-60,D5)
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,127
Members
452,381
Latest member
Nova88

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