98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel Formula
Excel Formula
Resolved · Urgent Priority · Version 2010
Mon 6 Nov 2017, 16:57Delegate Sanjay said...
Sanjay has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Formula
Hi,
I have to use multiple condition .
I have 2 column
Number of devices and days
Days is depend on Number of devices.
condition is "if the number of devices is between 0-39 then number of days would be 1,similarly if the devices are between 40 to 78 then number of days 2 and so on"
for an example if the if the number of devices would be 140 then the number of days should come 4.
Please update.
Thansk
Sanjay
Mon 6 Nov 2017, 19:43Trainer Jens said...
RE: Excel Formula
Hi Sanjay,
Thank you for the forum question.
I would use a Vlookup to do it.
Please see the yellow column sheet1 in attached file.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
devices days.xlsxTue 7 Nov 2017, 09:16Delegate Sanjay said...
RE: Excel Formula
Hi Jens,
I am not able to see the attached sheet,please let me know how I can see the sheet,alternately you can send the sheet to my email id "sanjaysharma_1978@hotmail.com".
Thanks
Sanjay
Tue 7 Nov 2017, 09:45Delegate Sanjay said...
RE: Excel Formula
Hi Jens,
i just got your sheet, but it looks I am not able to explain the requirement.
My requirement is if the number of devices are
1-39 then its should take 1 day if
40-78 then it should take 2 day if
79-117 then it should take 3 day and so on.
Please let me know if you need any other info.
Thanks
Sanjay
Tue 7 Nov 2017, 15:09
Edited on Tue 7 Nov 2017, 15:11Trainer Wendy said...
RE: Excel Formula
Hi Sanjay
Thank you for using our forum.
Jens is in training today and won't be able to respond
I would suggest using either a VLOOKUP or a nested IF statement.
Does the amount of devices listed have a limit?
If it does you could create a nested IF statement such as
=IF(A2<=39,1,IF(A2<=78,2,IF(A2<=117,3,IF(A2<=156,4,""))))
Or use an approximate match VLOOKUP as per the sheet attached
Hope that helps
Kind regards
Wendy
Attached files...
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Line breaks in a cellYou can control the line breaks for multiple-line headings or labels in your Microsoft Excel worksheet, just like you do in Microsoft Word. Here's how to do it. |
