Multiply If statements and Deleting digits

Mikest

New Member
Joined
Mar 5, 2002
Messages
20
This is a fantastic forum and thanks in advance for the answer to my questions.

1. I want to get a result a summary w/sheet to this scenario.

If E7=3,Project!A19:Q236,13 or IF e7=5,Project!A19:Q236,15 or E7=7,Project!A19:Q236,15
How do I get this to work.

2. I wish to delete the first or second digit plus the minus from this data (10-14 or 1-14) to give the result of 14

Once again thanks
Mike
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
On 2002-03-06 22:05, Mikest wrote:
This is a fantastic forum and thanks in advance for the answer to my questions.

1. I want to get a result a summary w/sheet to this scenario.

If E7=3,Project!A19:Q236,13 or IF e7=5,Project!A19:Q236,15 or E7=7,Project!A19:Q236,15
How do I get this to work.

2. I wish to delete the first or second digit plus the minus from this data (10-14 or 1-14) to give the result of 14

Once again thanks
Mike

should that first 13 be 15 ?

if so, try :

=IF(OR(E7={3;5;7}),sum(Project!A19:Q236),15)

(having a go at your second question)

Hope this helps
Chris
:)

2nd question :

assuming A1 holds your data, try this :

=REPLACE(A1,1,FIND("-",A1,1),"")+0

(any find and replace experts, please don't laugh !!)

:)
This message was edited by Chris Davison on 2002-03-06 22:54
This message was edited by Chris Davison on 2002-03-07 11:17
 
Upvote 0
Thanks Chris,

Question 2 answered perfectly.

Question 1.
Made a mistake on the last section should have read. 17 not 15.
Summary: if E7=3 then display data in col13
if E7=5 then display data in col15 and if E7=7 the display data in col17.

Thanks Again.
Mike
 
Upvote 0
Sounds like you want...

=INDEX(Project!A19:Q235,,E7+10)

...but, you said "summary" so the question is, "What kind of summary?" Perhaps, you want the sum...

=SUM(INDEX(Project!A19:Q235,,E7+10))
 
Upvote 0
I have a set of room numbers and measurements on a worksheet page called projects.

The idea is if a room has a cupboard 3 doors in it have a cell that tells me the size of the third door. (1 pair and 1 odd door)
Or if 5 doors the size of the fifth and so on.

All the third doors are in the 13th col, fifth doors are in 15 col and 7th doors are in the 17th col of the project w/sheet.

Thank again
Mike
 
Upvote 0
Well, if you want to list the 217 values associated with a cupboard type then select a vertical range of 217 cells and enter the array formula...

{=INDEX(Project!A19:Q235,,E7+10)}

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.
This message was edited by Mark W. on 2002-03-07 15:44
 
Upvote 0
Here's a quick sample from my Projects w/sheet
rm dr1 dr2 dr3
1 2 3 4 5 6 7 8 13
801B2,1,1,1400,3,1,Left,936,463,463,437

Then a have w/sheet called doors, where I enter the room No (801B2) and it displays all
the data I need. Cell E7 gives me how many doors.

Have a try at that,
Thanks
Mike
 
Upvote 0
On 2002-03-07 16:01, Mikest wrote:
Here's a quick sample from my Projects w/sheet
rm dr1 dr2 dr3
1 2 3 4 5 6 7 8 13
801B2,1,1,1400,3,1,Left,936,463,463,437

Then a have w/sheet called doors, where I enter the room No (801B2) and it displays all
the data I need. Cell E7 gives me how many doors.

Have a try at that,
Thanks
Mike

Well, let's see...

=INDEX(Project!A19:Q235,MATCH("801B2",A19:A235,0),E7+10)

...should give you the measurements for the doors in room 801B2.
This message was edited by Mark W. on 2002-03-07 16:10
 
Upvote 0
Thanks again all,

I have been working on the Foluma's suggested and get either a N/A or VAULE result.

Any Thoughts
Regards

Mike
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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